MySQL help

  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Okay, I'm really new to MySQL and I'm using phpadmin to do it. Now, I created the table and the fields but now I guess I'm supposed to be describing options for the data in the fields. I'm not sure what I'm supposed to put for length/values? I looked at the small help section and it confused me alot. I have 5 fields of decimal numbers between 20-0 and one field of text. I just reread what I typed and I doubt anyone will understand my question, lol, but if you can please help.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

This link gives complete details that you should really study to learn what the different field types and values are:

http://www.mysql.com/documentation/mysq ... lumn_types

However, to keep things relatively simple for now, for your number fields you should probably use tiny int with a length of 3 (the the length of 3 will allow for 3 colums of intigers, in other words, you could have the numbers 0- 999 in that field - you could do 2 which would allow 0-99) Attributes could be left blank. Null/not null - your choice. If you choose null, and there is not a value entered for a field the field will be left blank. If you choose not null the default will be 0 if no value is entered.

For the text field choose vchar and set a maximum length depending on how much text will be entered in that field. Try not to go overboard. Keeping the numbers small when possible improves the efficiency of the database, for example, if you don't expect the text field to ever be over 50 characters, then use 50 for the length value. Give yourself some leeway, but remember, the attributes can always be changed if you find in the future it's not sufficient.

I would suggest your first field in the table be set as your primary and often this is a field called "id" and is typically set to auto-increment this is beneficial if your table will be used to add additional rows. It makes referencing the data in the queries easier.

I know that's a really brief explanation, and I hope it doesn't confuse you more than it helps you. You're at a point where I was just a few months ago. I didn't understand any more than you did, and even now I'm still trying to get a handle on all the concepts. It comes down to taking some time to research and study.
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Thanks, I got past all of that and have made a table and have gotten all the fields and some data in it for a test. I then made a simple query and it has worked perfect. Now I'm trying to figure out how to get the query on my site. I went to export it and it gave me a bunch of options including SQL or XML. Each one puts out a long script thing, but when I try to insert into my website that is HTML is doesn't work and the script comes out as words. I also use Dreamweaver and I've been searching and do I have to connect to the database somehow with DW?
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Ok, I have a more specific question. What is a connection string and how to I found out what the connection string is to a database I made through myphpadmin?
  • RichB
  • Guru
  • Guru
  • User avatar
  • Posts: 1121
  • Loc: Boston

Post 3+ Months Ago

Connection string is more of an asp thing I think. If you want to connect to a mysql database from a php script you use the mysql functions that are built into php. I'd suggest downloading the php manual to check out the functions that are available as there are quite a few. The manual can be found at:

http://www.php.net/docs.php

A basic connection might look something like this:

Code: [ Select ]
<?php
$connection = mysql_connect("localhost", "userName", "password") or die("no connection");

$database = mysql_select_db("databaseName",$connection) or die("no database");

$query = "SELECT * FROM tableName";

$result = mysql_query($query) or die("no query");

while($row = mysql_fetch_array($result))
{
    $variable1 = $row["fieldName1"];
    $variable2 = $row["fieldName2"];
    
    echo $variable1;
    echo "<br>";
    echo $variable2;
}
mysql_close($connection);
?>
  1. <?php
  2. $connection = mysql_connect("localhost", "userName", "password") or die("no connection");
  3. $database = mysql_select_db("databaseName",$connection) or die("no database");
  4. $query = "SELECT * FROM tableName";
  5. $result = mysql_query($query) or die("no query");
  6. while($row = mysql_fetch_array($result))
  7. {
  8.     $variable1 = $row["fieldName1"];
  9.     $variable2 = $row["fieldName2"];
  10.     
  11.     echo $variable1;
  12.     echo "<br>";
  13.     echo $variable2;
  14. }
  15. mysql_close($connection);
  16. ?>


You would substitute your user name, password, database name, and names of your fields.

There are many different variations on this, and you should try searching google for php/mysql tutorial as there are a number of them available.

Dreamweaver may have some built-in ability to automate this, but I'm not really that familiar with it.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

DavyDuke17 wrote:
Each one puts out a long script thing, but when I try to insert into my website that is HTML is doesn't work and the script comes out as words.


Follow RichB's advice regarding connecting, but you should also rename yourpage.html to yourpage.php (or yourpage.asp - depending on what you're using).
  • RichB
  • Guru
  • Guru
  • User avatar
  • Posts: 1121
  • Loc: Boston

Post 3+ Months Ago

Maybe I misunderstood ( I seem to be doing a lot of that lately ) . I'm assuming that you are trying to connect to the database from a script in your page, and since you mentioned phpadmin I figured you were using php.

Anyway, if you do want to use asp then there is a site called http://www.connectionstrings.com that has examples of connection strings for different types of databases, and here is an article that describes how to connect using asp either with a Data Source Name (DSN) or without:

http://www.stardeveloper.com/articles/d ... 101&page=1
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Ok, I got it so that it connect to my database, but I'm having some problems having it put any data. I believe I ran the queries right, do I have to tell it to print back out the data? Is that what I'm missing? Here is the code I have so far, what is wrong?

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<? $hostname = "localhost";
$username = "******";
$password = "******";
$usertable = "stats";
$dbName = "vferraro_bballstats";

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");

//query details table begins
$sql = 'SELECT `Stats`.`Players` , `Stats`.`PPG` , `Stats`.`RPG` , `Stats`.`APG` ';
$sql .= 'FROM Stats';
$sql .= 'ORDER BY `Stats`.`PPG` DESC , `Stats`.`RPG` DESC , `Stats`.`APG` DESC LIMIT 0, 30';
?>
</body>
</html>
  • RichB
  • Guru
  • Guru
  • User avatar
  • Posts: 1121
  • Loc: Boston

Post 3+ Months Ago

You've setup the query, but you haven't actually run it. You would have to add something like this after you set up the $sql variable.

Code: [ Select ]
// send the query to the database
$result = mysql_query($sql) or die("Query failed");

// loop through the results
while($row = mysql_fetch_row($result))
{
    // extract the data from the row array
    // the first field will be $row[0], the second $row[1] etc.

    $stats = $row[0];
    // etc. etc.

    // print or echo the data to output it into the html page
    echo "$stats <br>";
    // etc. etc.
}
mysql_close();
  1. // send the query to the database
  2. $result = mysql_query($sql) or die("Query failed");
  3. // loop through the results
  4. while($row = mysql_fetch_row($result))
  5. {
  6.     // extract the data from the row array
  7.     // the first field will be $row[0], the second $row[1] etc.
  8.     $stats = $row[0];
  9.     // etc. etc.
  10.     // print or echo the data to output it into the html page
  11.     echo "$stats <br>";
  12.     // etc. etc.
  13. }
  14. mysql_close();
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

I tried to do this, I'm not sure if this is even remotely right, I kimd of used some php code from a couple different resources. When I run it, it says query failed - can someone explain what is wrong. Thanks for all your help RichB and ATNO/TW for all your help.

Code: [ Select ]
<? $hostname = "localhost";
$username = "******";
$password = "******";
$usertable = "stats";
$dbName = "vferraro_bballstats";

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");

//query details table begins
$sql = 'SELECT `Stats`.`Players` , `Stats`.`PPG` , `Stats`.`RPG` , `Stats`.`APG` ';
$sql .= 'FROM Stats';
$sql .= 'ORDER BY `Stats`.`PPG` DESC , `Stats`.`RPG` DESC , `Stats`.`APG` DESC LIMIT 0, 30';
// send the query to the database
$result = mysql_query($sql) or die("Query failed");
{
$variable1=$row["Players"];
$variable2=$row["PPG"];
$variable3=$row["RPG"];
$variable4=$row["APG"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("</tr>");
}
// loop through the results
while($row = mysql_fetch_row($result))
{
  // extract the data from the row array
  // the first field will be $row[0], the second $row[1] etc.

  $stats = $row[0];
  // etc. etc.

  // print or echo the data to output it into the html page
  echo "$stats <br>";
  // etc. etc.
}
mysql_close();

?>
  1. <? $hostname = "localhost";
  2. $username = "******";
  3. $password = "******";
  4. $usertable = "stats";
  5. $dbName = "vferraro_bballstats";
  6. MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
  7. @mysql_select_db( "$dbName") or die( "Unable to select database");
  8. //query details table begins
  9. $sql = 'SELECT `Stats`.`Players` , `Stats`.`PPG` , `Stats`.`RPG` , `Stats`.`APG` ';
  10. $sql .= 'FROM Stats';
  11. $sql .= 'ORDER BY `Stats`.`PPG` DESC , `Stats`.`RPG` DESC , `Stats`.`APG` DESC LIMIT 0, 30';
  12. // send the query to the database
  13. $result = mysql_query($sql) or die("Query failed");
  14. {
  15. $variable1=$row["Players"];
  16. $variable2=$row["PPG"];
  17. $variable3=$row["RPG"];
  18. $variable4=$row["APG"];
  19. //table layout for results
  20. print ("<tr>");
  21. print ("<td>$variable1</td>");
  22. print ("<td>$variable2</td>");
  23. print ("<td>$variable3</td>");
  24. print ("<td>$variable4</td>");
  25. print ("</tr>");
  26. }
  27. // loop through the results
  28. while($row = mysql_fetch_row($result))
  29. {
  30.   // extract the data from the row array
  31.   // the first field will be $row[0], the second $row[1] etc.
  32.   $stats = $row[0];
  33.   // etc. etc.
  34.   // print or echo the data to output it into the html page
  35.   echo "$stats <br>";
  36.   // etc. etc.
  37. }
  38. mysql_close();
  39. ?>
  • RichB
  • Guru
  • Guru
  • User avatar
  • Posts: 1121
  • Loc: Boston

Post 3+ Months Ago

Try this:
Code: [ Select ]
<?
$hostname = "localhost";
$username = "******";
$password = "******";
$dbName = "vferraro_bballstats";

mysql_connect($hostname, $username, $password) or die("DB connection unavailable");
mysql_select_db( "$dbName") or die( "Unable to select database");

//query details table begins
$sql = "SELECT `Stats`.`Players` , `Stats`.`PPG` , `Stats`.`RPG` , `Stats`.`APG` ";
$sql .= "FROM Stats ";
$sql .= "ORDER BY `Stats`.`PPG` DESC , `Stats`.`RPG` DESC , `Stats`.`APG` DESC LIMIT 0, 30";

// send the query to the database
$result = mysql_query($sql) or die("Query failed");

// loop through the results
while($row = mysql_fetch_array($result))
{
    // extract the field data
    $variable1=$row["Players"];
    $variable2=$row["PPG"];
    $variable3=$row["RPG"];
    $variable4=$row["APG"];

    print ("<tr>");
    print ("<td>$variable1</td>");
    print ("<td>$variable2</td>");
    print ("<td>$variable3</td>");
    print ("<td>$variable4</td>");
    print ("</tr>");
}

mysql_close();
?>
  1. <?
  2. $hostname = "localhost";
  3. $username = "******";
  4. $password = "******";
  5. $dbName = "vferraro_bballstats";
  6. mysql_connect($hostname, $username, $password) or die("DB connection unavailable");
  7. mysql_select_db( "$dbName") or die( "Unable to select database");
  8. //query details table begins
  9. $sql = "SELECT `Stats`.`Players` , `Stats`.`PPG` , `Stats`.`RPG` , `Stats`.`APG` ";
  10. $sql .= "FROM Stats ";
  11. $sql .= "ORDER BY `Stats`.`PPG` DESC , `Stats`.`RPG` DESC , `Stats`.`APG` DESC LIMIT 0, 30";
  12. // send the query to the database
  13. $result = mysql_query($sql) or die("Query failed");
  14. // loop through the results
  15. while($row = mysql_fetch_array($result))
  16. {
  17.     // extract the field data
  18.     $variable1=$row["Players"];
  19.     $variable2=$row["PPG"];
  20.     $variable3=$row["RPG"];
  21.     $variable4=$row["APG"];
  22.     print ("<tr>");
  23.     print ("<td>$variable1</td>");
  24.     print ("<td>$variable2</td>");
  25.     print ("<td>$variable3</td>");
  26.     print ("<td>$variable4</td>");
  27.     print ("</tr>");
  28. }
  29. mysql_close();
  30. ?>


I accidentally showed two different methods of looping through the results in the earlier examples (there are several variations). Either one will work, but you don't need to have both. One method uses $row = mysql_fetch_row and then you specify the fields by number starting at $row[0]. The other method uses $row = mysql_fetch_array where you specify the fields by $row["fieldName"].

This example uses the fetch_array and specify by name approach, which I generally prefer.

I think the only thing wrong with your query string was that you were missing a space after Stats in the second line.
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Thanks so much, I got it working perfectly now thanks to you. One last question, for the type of field I wanted a decimal numbers like 4.4 or 1.7 and so on so I had type decimal but it always makes the number round to an integer. Is there a different type that won't do that? Well, thanks so much I can't believe it finally works. :D
  • RichB
  • Guru
  • Guru
  • User avatar
  • Posts: 1121
  • Loc: Boston

Post 3+ Months Ago

You can edit the decimal fields to set the number of characters after the decimal point that you want to show (apparently the default is zero, which is why none have been stored). Go to the structure tab in phpmyadmin, click on the edit/change icon for the field that you want to edit and in Length/Values column you will see something like 10,0 where 10 is the number that you set for display size originally (10 is the default) and the second number is the number of digits after the decimal point. Change the zero to 1 or however many digits you want to show after the decimal point. When you go back to the structure page you should see that the default value has changed to 0.0 or 0.00 depending on how many decimal values you want to show.

Unfortunately, I think you may have to reenter your data as I think the decimal portion was lost when the second value was set to zero. Try setting the second number to 1 or whatever and then displaying the data to see if the portion after the decimal point is still in there. If not then I think you will have to enter the values again.
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

I only had test data in so far, and I know exacly what you mean, Thanks so much.

Post Information

  • Total Posts in this topic: 14 posts
  • Users browsing this forum: No registered users and 70 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.