LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 05-29-2003, 08:03 AM   #1
zuessh
Member
 
Registered: Jun 2002
Location: USA
Distribution: Suse 8.0
Posts: 247

Rep: Reputation: 30
pulling data from mysql database


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
 
Old 05-29-2003, 08:08 AM   #2
jharris
Senior Member
 
Registered: May 2001
Location: Bristol, UK
Distribution: Slackware, Fedora, RHES
Posts: 2,243

Rep: Reputation: 46
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...
 
Old 05-29-2003, 08:28 AM   #3
zuessh
Member
 
Registered: Jun 2002
Location: USA
Distribution: Suse 8.0
Posts: 247

Original Poster
Rep: Reputation: 30
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

Last edited by zuessh; 05-29-2003 at 08:30 AM.
 
Old 05-29-2003, 08:32 AM   #4
jharris
Senior Member
 
Registered: May 2001
Location: Bristol, UK
Distribution: Slackware, Fedora, RHES
Posts: 2,243

Rep: Reputation: 46
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...
 
Old 05-29-2003, 09:08 AM   #5
zuessh
Member
 
Registered: Jun 2002
Location: USA
Distribution: Suse 8.0
Posts: 247

Original Poster
Rep: Reputation: 30
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
 
Old 05-29-2003, 09:13 AM   #6
jharris
Senior Member
 
Registered: May 2001
Location: Bristol, UK
Distribution: Slackware, Fedora, RHES
Posts: 2,243

Rep: Reputation: 46
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...
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Qmail + vQregister not entering data into database lsimon4180 Linux - Software 0 03-25-2005 01:46 PM
Writing an app that uses a mysql database without installing mysql server? QtCoder Programming 4 08-09-2004 02:43 PM
MYSQL help - mysql database missing eloviyandhi Linux - Software 1 03-20-2004 09:20 PM
After insert data, Database is empty ivankong Linux - Software 2 06-16-2003 07:35 AM
Manage data and maintenance of MySQL database online rhuser Linux - Software 2 02-17-2003 08:31 PM


All times are GMT -5. The time now is 05:46 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration