using PHP and MySQL to build a web query

I have been staring at this for far too long to admit, but I am stumped.  I am a newbie to PHP and MySQL but have a relatively good grasp on SQL, so I would like to think I know what I am doing here.  Unfortunately, results prove different.

I have built a simple form to submit information into a very simple MySQL dbase - will expand it later when the client firms up the requirements.  The submission part works.

Now to pull the results.  I can get a simple 'SELECT * FROM table' to work, but when I try to insert a WHERE parameter, I get a syntax error.

Here is the code on the page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Dynamic Menu</title>
</head>

<body>

<?
// Make a MySQL Connection
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM test1 WHERE show = '1'")
or die(mysql_error());  

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry

while($row = mysql_fetch_assoc($result))
{
    echo "Title :{$row['title']} <br>" .
         "Description : {$row['description']} <br>" .
         "Price : {$row['price']} <br><br>";
}

?>



</body>
</html>

Like I said - this works fine until I add the 'WHERE show = '1'.

My thinking is the show field in the dbase table is a bit flag - true or false - that indicates if a particular entry should be visible or not.  I have tried setting the field type to tinyint (1 or 0), varchar (1 or 0), or boolean.  I have tried permutations of 'show is 1', 'show is true', 'show > 0' and 'show = '1' '.

I hate to say how much time I have spent on this one particular piece - it is killing me.  As near as I can tell, the PHP/MYSQL dbase does not have bit value, so should I be using tinyint?  VARCHAR?  What is the syntax to successfully query against it?

Thanks in advance!
chipsterva69Asked:
Who is Participating?
 
jayarjoConnect With a Mentor Commented:
Your query is ok. You get an error probably because - show - is a reserved word. Enclose it within backquotes, like this: `show` = '1' and your error should go away.

It's general best practice by the way to enclose our filed in backquotes.
0
 
jayarjoCommented:
our filed = your fields :D

damn typos...
0
 
Kruger_monkeyConnect With a Mentor Commented:
Original question aside, which javarjo seems to have sorted, and bearing in mind I'm a newbie too, won't the following cause you problems, or cause you to loose the first returned row, also not sure why fetch_array in first row, but fetch_assoc in 2nd?

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry

while($row = mysql_fetch_assoc($result))

I ran into this when I first started, I was doing $row=... followed by while $row... which caused me to loose the first row everytime as I'd been reserving it with the first $row.

Might not be a problem in your case, as I said am a newbie, just noticed it because I'd had that problem.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
chipsterva69Author Commented:
okay - the error is gone!  thanks a million!

follow-up though - when I run the query against '0' in the now-named isVisible field, it shows everything listed with the 0 value.  I use '1' and the one item with that value does not show up.  is this a related problem?  The isVisible field is a tinyint, with values of 1 or 0.
0
 
jayarjoConnect With a Mentor Commented:
It actually depends on what you are trying to achieve. But generally you didn't get into the details of original mysql_fetch_array(), did you? :)

mysql_fetch_assoc and mysql_fetch_array are equal only if you pass second argument to mysql_fetch_array like this:

mysql_fetch_array($result, MYSQL_ASSOC);

By default it behaves a bit differently. Use print_r on your $row variable to actually see the difference and maybe read this: http://php.net/manual/en/function.mysql-fetch-array.php. Apart from descriptions they got great examples and valuable comments .
0
 
chipsterva69Author Commented:
kruger_monkey - that sounds like the problem I just came followed up with - an expected return of one item isn't showing.  How do I avoid the problem?
0
 
Kruger_monkeyCommented:
Drop the first $row= and only use the while $row option.

Caught me out a few times.  

$row = mysql_fetch_association($query)  This line will take the first row and assign it here, so drop this.

Only use

while($row......) you will get the first line back again. Basically the following should work, I believe.

// store the record of the "example" table into $row
//$row = mysql_fetch_array( $result );  -- either comment out or remove this line.
// Print out the contents of the entry

while($row = mysql_fetch_assoc($result))
{
    echo "Title :{$row['title']} <br>" .
         "Description : {$row['description']} <br>" .
         "Price : {$row['price']} <br><br>";
}

0
 
chipsterva69Author Commented:
kruger_monkey - thanks!  that is just what I did.  works like a charm.  thanks a million for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.