PHP SQL - Newbie problem - simple SELECT statement from PHP.

Author

Newbie problem - simple SELECT statement from PHP.

Steve T

2005-04-22, 3:56 am

I have set up a database (on my ISP's server) with one table ("Houses") and
populated that table with 2 rows using phpMyAdmin. When I run a very simple
query on it -

SELECT * FROM Houses

- from within phpMyAdmin I get the result I expect, in this case 2 rows. But
when I try to send this query from a PHP script I get back zero rows.

None of the code is crashing or generating an error so I'm guessing that I
am not managing to properly encapsulate the SQL within PHP? Here was my
attempt (with preliminary code for completeness).

/* Declare database parameters */
$db_host = "rumpus";
$username = "steventownley";
$password = // removed from this email!
$db_name = "steventownley_pn";

/* Set up database connection */
mysql_connect($db_host, $username, $password);
mysql_select_db($db_name);

/* Set up query */
$query="SELECT * FROM Houses;";

/* Send query to database */
$result = mysql_query($query);
if($result) {
echo "query completed OK. ";
}
else {
echo "query failed! ";
}

Thanks

Steve

--
----
Steven Townley
Stockport, England
MSN Messenger: steven@czechbook.freeserve.co.uk

Sander Van de Moortel

2005-04-22, 3:56 am

Steve T wrote:
> I have set up a database (on my ISP's server) with one table ("Houses") and
> populated that table with 2 rows using phpMyAdmin. When I run a very simple
> query on it -
>
> SELECT * FROM Houses
>
> - from within phpMyAdmin I get the result I expect, in this case 2 rows. But
> when I try to send this query from a PHP script I get back zero rows.
>
> None of the code is crashing or generating an error so I'm guessing that I
> am not managing to properly encapsulate the SQL within PHP? Here was my
> attempt (with preliminary code for completeness).
>
> /* Declare database parameters */
> $db_host = "rumpus";
> $username = "steventownley";
> $password = // removed from this email!
> $db_name = "steventownley_pn";
>
> /* Set up database connection */
> mysql_connect($db_host, $username, $password);
> mysql_select_db($db_name);
>
> /* Set up query */
> $query="SELECT * FROM Houses;";
>
> /* Send query to database */
> $result = mysql_query($query);
> if($result) {
> echo "query completed OK. ";
> }
> else {
> echo "query failed! ";
> }
>
> Thanks
>
> Steve
>
>
>
What if you changed

$query="SELECT * FROM Houses;";

into

$query="SELECT * FROM Houses";

(remove the semicolon there, you can also add ´ ´ this kind of quotes
around the tablename to tell MySQL that it's a table you're looking
for). Just another hint: never use SELECT * FROM, but specify the
columns you want one by one. This will save unnecessary database time
and make it easier for you to recall how the database looks like,
straight from the PHP script.

S

Steve T

2005-04-22, 8:56 am

"Sander Van de Moortel" <sander@jnm.be> wrote in message
news:42684108$1@griseus.its.uu.se...
> Steve T wrote:
> What if you changed
>
> $query="SELECT * FROM Houses;";
>
> into
>
> $query="SELECT * FROM Houses";
>
> (remove the semicolon there, you can also add ´ ´ this kind of quotes
> around the tablename to tell MySQL that it's a table you're looking for).
> Just another hint: never use SELECT * FROM, but specify the columns you
> want one by one. This will save unnecessary database time and make it
> easier for you to recall how the database looks like, straight from the
> PHP script.
>
> S

Thanks.

Removing the semi-colon didn't fix it.

This may sound strange but I'm having trouble identifying the type of
"ticks" or "apostrophes" or "quote marks" you are using here!

I tried...

$query="SELECT * FROM `Houses`"; // uses back ticks like PHP execution
operator

....which had no discernible effect (query still returned zero rows).

I also tried...

$query="SELECT * FROM 'Houses'"; // single quote marks.

....which caused the call to mysql_query to fail (returned 0).

Neither of these ticks/apostrpohes/quote-marks looks exactly like the one
you have used.

Are either of them correct? I can't find any others on my keyboard!

no@emails.thx

2005-04-22, 3:58 pm

On Fri, 22 Apr 2005 09:50:27 +0100, "Steve T"
< pedallingminstrel@NOSPAMpedallingminstre
l.fsworld.co.uk> wrote:

> /* Declare database parameters */
> $db_host = "rumpus";
> $username = "steventownley";
> $password = // removed from this email!
> $db_name = "steventownley_pn";
>
> /* Set up database connection */
> mysql_connect($db_host, $username, $password);
> mysql_select_db($db_name);
>
> /* Set up query */
> $query="SELECT * FROM Houses;";
>
>/* Send query to database */
> $result = mysql_query($query);
> if($result) {
> echo "query completed OK. ";
> }
> else {
> echo "query failed! ";
> }

Hi Steve

The two types of quote used are speach marks " and apostrophy '

I am no MySQL guru but isn't there a fundamental problem here with
your code? I mean in my books mysql_connect() returns a database
connection index, which you then pass as an argument to
mysql_select_db() and mysql_query(). Like:

$conn = mysql_connect($db_host, $username, $password);
mysql_select_db($db_name,$conn);
$result = mysql_query('SELECT * FROM Houses',$conn);

Chris

Steve T

2005-04-22, 3:58 pm

<no@emails.thx> wrote in message
news:l6uh61p6d66na8j0a2ultafotb5c0icjf8@
4ax.com...
> On Fri, 22 Apr 2005 09:50:27 +0100, "Steve T"
> < pedallingminstrel@NOSPAMpedallingminstre
l.fsworld.co.uk> wrote:
>
>
> Hi Steve
>
> The two types of quote used are speach marks " and apostrophy '
>
> I am no MySQL guru but isn't there a fundamental problem here with
> your code? I mean in my books mysql_connect() returns a database
> connection index, which you then pass as an argument to
> mysql_select_db() and mysql_query(). Like:
>
> $conn = mysql_connect($db_host, $username, $password);
> mysql_select_db($db_name,$conn);
> $result = mysql_query('SELECT * FROM Houses',$conn);
>
> Chris

Thanks Chris.

What you say makes sense. I have come across at least three different and/or
conflicting ways of doing this now, each with slightly different syntax in
the mysql function calls. So I was putting the differences down to different
versions of PHP (possibly mistakenly).

Anyway I tried what you suggest and

ly it made no difference, i.e. the
query didn't return an error but it still returned zero rows (when I know
there are 2 rows in the table Houses).

Tony

2005-04-24, 3:56 pm

Steve T wrote:
> <no@emails.thx> wrote in message
> news:l6uh61p6d66na8j0a2ultafotb5c0icjf8@
4ax.com...

<snip>

> Anyway I tried what you suggest and

ly it made no difference, i.e. the
> query didn't return an error but it still returned zero rows (when I know
> there are 2 rows in the table Houses).

CREATE TABLE `houses` (
`house_num` INT NOT NULL ,
`house_street` VARCHAR( 125 ) NOT NULL
);

INSERT INTO `houses`
( `house_num` , `house_street` )
VALUES
('23', 'some_street'),
('45', 'other_street');

Then in PHP:

<?php

$conn = mysql_connect("db_servername","db_user", "password")
or die("Error:" . mysql_error());

mysql_select_db("test") or die(mysql_error());
$result = mysql_query
("SELECT house_num,house_street FROM houses")
or die(mysql_error());

if (mysql_num_rows($result) == 0)
{ echo "No record found"; }
else
{ while ($row = mysql_fetch_assoc($result))
{
echo "house_num => ".$row["house_num"]." ";
echo "house_street => ".$row["house_street"]." ";
}
mysql_free_result($result);
}
mysql_close($conn);
?>

Maybe buy a good book http://tinyurl.com/dno22
or alternatively start with the current manuals:

http://www.php.net/docs.php
http://dev.mysql.com/doc/

Anyways, hope that helps get you started.

Steve T

2005-05-01, 9:00 pm

"Tony" <spamkill@nospam.net> wrote in message
news:huRae.3894$TT6.2477@newsfe3-win.ntli.net...
> Steve T wrote:
>
> <snip>
>
>
>
>
> CREATE TABLE `houses` (
> `house_num` INT NOT NULL ,
> `house_street` VARCHAR( 125 ) NOT NULL
> );
>
>
> INSERT INTO `houses`
> ( `house_num` , `house_street` )
> VALUES
> ('23', 'some_street'),
> ('45', 'other_street');
>
>
> Then in PHP:
>
> <?php
>
> $conn = mysql_connect("db_servername","db_user", "password")
> or die("Error:" . mysql_error());
>
> mysql_select_db("test") or die(mysql_error());
> $result = mysql_query
> ("SELECT house_num,house_street FROM houses")
> or die(mysql_error());
>
>
> if (mysql_num_rows($result) == 0)
> { echo "No record found"; }
> else
> { while ($row = mysql_fetch_assoc($result))
> {
> echo "house_num => ".$row["house_num"]." ";
> echo "house_street => ".$row["house_street"]." ";
> }
> mysql_free_result($result);
> }
> mysql_close($conn);
> ?>
>
>
>
> Maybe buy a good book http://tinyurl.com/dno22
> or alternatively start with the current manuals:
>
> http://www.php.net/docs.php
> http://dev.mysql.com/doc/
>
>
> Anyways, hope that helps get you started.

Thanks for the advice. Interestingly I already have the book you recommend,
but that caused me a lot of problems as either it is full of syntax errors
or the syntax is PHP5 and my ISP supports only PHP4. Anyway, I got past that
little hitch, and have tried to adapt the code you supplied (above) to
access my database table.

When calling this code from a PHP script I am still unable to get my
database to return any rows, even though the SQL statement works in
PlusNet's phpMyAdmin page. I can insert data into the table but can not
retrieve it.

Code excerpts below. NB: NONE of the code below produces errors or fails. It
all appears to work but always returns zero rows from the query.

/* Connect to database server */
$conn = mysql_connect($db_host, $username, $password);
if (!$conn) {
die('Could not connect: ' . mysql_error());
}

/* Specify database to be accessed */
$db_selected = mysql_select_db($db_name,$conn);
if (!$db_selected) {
die ('Can't use that database : ' . mysql_error());
}

/* Send query to database */
$result = mysql_query("SELECT * FROM Houses", $conn); // $conn is optional
here, omitting it makes no difference
if($result) {
echo "query completed OK. ";
}
else {
echo 'query failed!' . mysql_error().' ';
}

/* Get the number of rows returned */
$numresults = mysql_num_rows($result); // This always returns zero!!!

All advice welcome.

Thanks

Steve

coolsti

2005-05-02, 8:56 am

On Sat, 30 Apr 2005 14:20:48 +0100, Steve T wrote:

> When calling this code from a PHP script I am still unable to get my
> database to return any rows, even though the SQL statement works in
> PlusNet's phpMyAdmin page. I can insert data into the table but can not
> retrieve it.
>
> Code excerpts below. NB: NONE of the code below produces errors or fails. It
> all appears to work but always returns zero rows from the query.
>
> /* Connect to database server */
> $conn = mysql_connect($db_host, $username, $password);
> if (!$conn) {
> die('Could not connect: ' . mysql_error());
> }
>
> /* Specify database to be accessed */
> $db_selected = mysql_select_db($db_name,$conn);
> if (!$db_selected) {
> die ('Can't use that database : ' . mysql_error());
> }
>
> /* Send query to database */
> $result = mysql_query("SELECT * FROM Houses", $conn); // $conn is optional
> here, omitting it makes no difference
> if($result) {
> echo "query completed OK. ";
> }
> else {
> echo 'query failed!' . mysql_error().' ';
> }
>
> /* Get the number of rows returned */
> $numresults = mysql_num_rows($result); // This always returns zero!!!
>
> All advice welcome.
>
> Thanks
>
> Steve

Sounds strange. Are you sure that the user $usename has been granted the
permission in mysql to perform a select from this database? I am not sure
what $result would be if this is not the case.

Or you can try this: change your query to "SELECT count(*) from Houses".
This should give back one row if things are working properly. The returned
row contains the number of rows found that match your query. Or to see if
php can even communicate with your database, try a query like

"DESCRIBE Houses"

which should return a row for each column in the Houses table.

- steve

Sponsored Links