pulling data from mysql database

zuessh · 05-29-2003, 08:03 AM

This is somewhat of a double post, so let me go ahead and apologize. I have the following php script to pull data from a mysql database:

<?

$db_name = "php";
$db_user = "testphp";
$db_pass = "test";

$connection = mysql_connect("localhost", "$db_user", "$db_pass") or die("Couldn't");
$db = mysql_select_db($db_name, $connection) or die("Couldn't Select Database");

$sql = "SELECT * from phptest";
$result = mysql_query($sql, $connection)or die ("Couln't execute query.");
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$name = $row['name'];

$text = "$id $name <br>";
}
echo $text;
?>

the problem is, it only displays the very last row from the table. I have five rows and would like for it to show the id and name from all five. Any suggestions? Thanks

jharris · 05-29-2003, 08:08 AM

Its not working becuase you echo $text line is outside of your while loop! So all you're printing is the final value written to the $text variable.

What you want is

PHP Code:


			
<?



$db_name = "php";

$db_user = "testphp";

$db_pass = "test";



$connection = mysql_connect("localhost", "$db_user", "$db_pass") or die("Couldn't");

$db = mysql_select_db($db_name, $connection) or die("Couldn't Select Database");



$sql = "SELECT * from phptest";

$result = mysql_query($sql, $connection)or die ("Couln't execute query.");

while ($row = mysql_fetch_array($result)) {

$id = $row['id'];

$name = $row['name'];



$text = "$id $name <br>";

echo $text;

}

?>

cheers

Jamie...

zuessh · 05-29-2003, 08:28 AM

Thanks, that did it. To take it a step further for a

if i want to only pull one row, why can i not change

$sql = "SELECT * from phptest";

to

$sql = "SELECT test from phptest";

and it only pull back the row with the id and name of test? When i do so, it comes back 'cant execute query'

Thanks

jharris · 05-29-2003, 08:32 AM

Sounds to me like you want something like

Code:

select * from phptest where name = "test";

on the assumption that you want all rows where name = "test". If you just want the first occurance then you'll want

Code:

select * from phptest where name = "test" limit 1;

Sounds to me like you need to readup on SQL!

cheers

Jamie...

zuessh · 05-29-2003, 09:08 AM

You are correct, I need to read on sql and php. Any good suggestions, other than webmonkey? Couple more questions

When i add

$sql = "select * from phptest where id = 1";

it returns the correct information, however when i add

$sql = "select * from phptest where name = Test";

it comes back 'could not execute query'.

Thanks for all the help

jharris · 05-29-2003, 09:13 AM

Quote:

Originally posted by zuessh
$sql = "select * from phptest where name = Test";

Should be

Code:

$sql = "select * from phptest where name = "Test";

You need to quote strings. Remember that if you doing this via PHP and you are holding your query in a variable first then you'll need to either delimit the speachmarks or user single quotes, so

PHP Code:


			
$query = "select * from phptest where name = \"test\"";

or

PHP Code:


			
$query = "select * from phptest where name = 'test'";

As for SQL resources, I'm not sure really, what do you get if you just google for "SQL tutorial"? I'd guess that you might hit some good material at universities as basic SQL seems a favourite on many computing related degrees.

cheers

Jamie...