query for showing data? please help

[Richlilley]

Hello I am new to php and mysql, trying to learn fast and take it all in! I have set up the sql database and want the person when they click on the category link to be able to see each product in that categoer printed one after the other on the screen!

 

I have the code like this:-

 

<?php

 

 

// open connection

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

 

// select database

mysql_select_db($db) or die ("Unable to select database!");

 

 

// create query

if (! isset($search)) {

$query = "SELECT * FROM  `Products` WHERE  `Category` LIKE  '$cat%'";

} else {

$query = "SELECT * FROM  `Products` WHERE  `Description` LIKE  '%$search%'";

 

}

 

 

// execute query

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

 

// see if any rows were returned

if (mysql_num_rows($result) > 0) {

    // yes

    // print them one after another

echo "<span class='titlestyle'>$cat</span>";

ECHO "<table width='90%' height='257' border='0'>";

 

 

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

 

  ECHO "<tr>";

    ECHO "<td height='36' colspan='4'><span class='titlestyle'>$row[Title]</span></td>";

    ECHO "</tr>";

  ECHO "<tr>";

    ECHO "<td colspan='2' rowspan='2'><a href=";

echo "javascript:popupWindow('";

echo "productimages/$row[image]',400,400);";

echo "><img src='productimages/$row[image]' width='150' height='150'></a></td>";

  ECHO "<td width='68%' height='127' colspan='2'><span class='descriptionstyle'>$row[Description]</span></td>";

    ECHO "</tr>";

 

  ECHO "<tr>";

    ECHO "<td>£$row[Price]</td>";

    ECHO "<td><a href='add-to-basket.php?productID=$row[0]&cat=$cat'><img src='images/add-icon.gif' width='95' height='19'></a></td>";

  ECHO "</tr>";

  ECHO "<tr>";

    ECHO "<td colspan='4'><hr></td>";

   

  ECHO "</tr>";

 

  }

 

ECHO "</table>";

}

else {

    // no

    // print status message

    echo "<p align='center'><font color='#808080' face='Arial'>Sorry no products found</font></p>";

}

 

// free result set memory

mysql_free_result($result);

 

// close connection

mysql_close($connection);

 

 

 

?>

 

on the website I am getting a error message as:-

 

Error in query: SELECT * FROM `Category` WHERE `Category` LIKE 'BOYS SHORTS%'. Unknown column 'Category' in 'where clause'

 

please help please...

 

Rich

[Anzeo]

Put your code in between the code tags when posting it up here please thanks.

 

Furthermore you don't need to parse all the html code through php, use this instead (snippet):

 

<table>
<?php
while($row = mysql_fetch_array($result)) {
?>
<tr><td><?php echo "$yourvariable1"</td></tr>
<?php } ?>
</table

also: check if you have a column named category in your table Products

[Richlilley]

Thanks ill try your suggestion! Ill get there.

 

Rich

[bobleny]

Here is one problem so far. When you insert a variable into an echo, you need to break it up.

 

Lines like this:

ECHO "<td height='36' colspan='4'><span class='titlestyle'>$row[Title]</span></td>";

 

Need to be like this:

ECHO "<td height='36' colspan='4'><span class='titlestyle'>" . $row[Title] . "</span></td>";

 

Heres a quick little thing.

 

<?php
$dave = "David";
$john = "John";

//This is correct
echo "Hi, my name is " . $dave . ". Who are you?";
echo "<br />";
echo "Hi " . $dave . ". My name is " . $john;
//End

echo "<br /><br />";

//This is incorrect
echo "Hi, my name is $dave. Who are you?";
echo "<br />";
echo "Hi $dave. My name is $john";
//End
?>

 

Both of those will probably work, but the "incorrect" way stands a chance of not working from time to time which will really screw with you!

 

----------------

Anzeo is right, if you have a lot of html and only a few variables, it is easier to simply close your php for the time being.

---------------

 

With out seeing your database, it is best to query like this:

$query = "SELECT * FROM products WHERE category = cat";

 

Rather than:

$query = "SELECT * FROM  `Products` WHERE  `Category` LIKE  '$cat%'";

--------------

Heres a few things that will help you out! They all pertain to the problems you are currently having so check them out.

http://www.ilovejackdaniels.com/mysql_cheat_sheet.png

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html