PHP Mysql "Where" statement

Question

duttydea 0 Newbie Poster

18 Years Ago

I am a Newbie To PHP!!

I used maestro php myqsl to crate the code below..

I am trying to change the query to include a where statment:

"where Product_Category = Acoustic treatment"

everytime i edit it i get a syntax Error!!

please can you help?

Thanks in advance

<?php session_start();

  if (isset($_GET["getimage"]))
  {
    $conn = connect();
    getimage(@$_GET["getimage"], @$_GET["field"]);
    exit;
  }
?>

<html>
<head>
<title>sounscou_Datapoint -- prod_listing</title>
<meta name="generator" http-equiv="content-type" content="text/html">
<style type="text/css">
  body {
    background-color: #FFFFFF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
  .bd {
    background-color: #FFFFFF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
  .tbl {
    background-color: #FFFFFF;
  }
  a:link { 
    background-color: #FFFFFF01;
    color: #0000CD;
    font-family: Arial;
    font-size: 12px;
  }
  a:active { 
    background-color: #FFFFFF01;
    color: #0000FF;
    font-family: Arial;
    font-size: 12px;
  }
  a:visited { 
    background-color: #FFFFFF01;
    color: #800080;
    font-family: Arial;
    font-size: 12px;
    font-style: italic;
  }
  .hr {
    background-color: #000000;
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:link {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:active {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:visited {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  .dr {
    background-color: #FFFFFF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
  .sr {
    background-color: #F3F3F3;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
    font-weight: bold;
  }
</style>
</head>
<body>

<p>
  <a href="http://localhost/sounscou_Datapoint/prod_listing.php?a=view&recid=77">http://localhost/sounscou_Datapoint/prod_listing.php?a=view&amp;recid=77</a>
  <?php
  $conn = connect();
  $showrecs = 10;
  $pagerange = 10;

  $a = @$_GET["a"];
  $recid = @$_GET["recid"];
  $page = @$_GET["page"];
  if (!isset($page)) $page = 1;

  switch ($a) {
    case "view":
      viewrec($recid);
      break;
    default:
      select();
      break;
  }


  mysql_close($conn);
?>
</p>
<p>
  
  
  
  <?php function select()
  {
  global $a;
  global $showrecs;
  global $page;

  $res = sql_select();
  $count = sql_getrecordcount();
  if ($count % $showrecs != 0) {
    $pagecount = intval($count / $showrecs) + 1;
  }
  else {
    $pagecount = intval($count / $showrecs);
  }
  $startrec = $showrecs * ($page - 1);
  if ($startrec < $count) {mysql_data_seek($res, $startrec);}
  $reccount = min($showrecs * $page, $count);
?>
</p>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
  <td><div align="center">Complete Product Listing</div></td>
</tr>
<tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
</table>
<hr size="1" noshade>
<div align="center">
  <?php showpagenav($page, $pagecount); ?>
  <table class="bd" border="0" cellspacing="1" cellpadding="4">
    <tr>
      <?php if ($page > 1) { ?>
      <td><a href="prod_listing.php?page=<?php echo $page - 1 ?>">&lt;&lt;&nbsp;Prev</a>&nbsp;</td>
      <?php } ?>
      <?php
  global $pagerange;

  if ($pagecount > 1) {

  if ($pagecount % $pagerange != 0) {
    $rangecount = intval($pagecount / $pagerange) + 1;
  }
  else {
    $rangecount = intval($pagecount / $pagerange);
  }
  for ($i = 1; $i < $rangecount + 1; $i++) {
    $startpage = (($i - 1) * $pagerange) + 1;
    $count = min($i * $pagerange, $pagecount);

    if ((($page >= $startpage) && ($page <= ($i * $pagerange)))) {
      for ($j = $startpage; $j < $count + 1; $j++) {
        if ($j == $page) {
?>
      <td><b><?php echo $j ?></b></td>
      <?php } else { ?>
      <td><a href="prod_listing.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
      <?php } } } else { ?>
      <td><a href="prod_listing.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
      <?php } } } ?>
      <?php if ($page < $pagecount) { ?>
      <td>&nbsp;<a href="prod_listing.php?page=<?php echo $page + 1 ?>">Next&nbsp;&gt;&gt;</a>&nbsp;</td>
      <?php } ?>
    </tr>
  </table>
  <table class="tbl" border="0" cellspacing="1" cellpadding="6"width="90%">
    <?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = mysql_fetch_assoc($res);
    $style = "dr";
    if ($i % 2 != 0) {
      $style = "sr";
    }
?>
    <tr bordercolor="#000000" bgcolor="#FFFFFF">
      <td width="4%" rowspan="2" class="<?php echo $style ?>"><div align="center"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="prod_listing.php?a=view&recid=<?php echo $i ?>"><?php echo outimage($i, "Product_Picture", "", "", "") ?></a></font></div></td>
      <td width="22%" height="30" class="<?php echo $style ?>"><div align="center">
        <p><font size="-3" face="Verdana, Arial, Helvetica, sans-serif"><?php echo htmlspecialchars($row["Product_Name"]) ?></font></p>
      </div></td>
      <td width="39%" rowspan="2" class="<?php echo $style ?>"><font size="-3" face="Verdana, Arial, Helvetica, sans-serif"><?php echo htmlspecialchars($row["Product_Description"]) ?><a href="prod_listing.php?a=view&recid=<?php echo $i ?>">View</a></font></td>
      <td width="6%" rowspan="2" class="<?php echo $style ?>"><font size="-3" face="Verdana, Arial, Helvetica, sans-serif"><a href="prod_listing.php?a=view&recid=<?php echo $i ?>"><img src="images/info.new.norm.gif" alt="view info" name="Info" width="34" height="31" border="0"></a></font></td>
      <td width="18%" rowspan="2" class="<?php echo $style ?>"><div align="center"><font size="-3" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;<font color="#0000FF"><?php echo htmlspecialchars($row["Product_Category"]) ?> </font><font color="#000000"><strong>&gt;</strong></font><font color="#0099FF"> <?php echo htmlspecialchars($row["Product_subcat"]) ?> </font></font></div></td>
      <td width="11%" rowspan="2" class="<?php echo $style ?>"><font size="-3" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;&nbsp;<a href="<?php echo htmlspecialchars($row["Product_Cart"]) ?>"><img src="images/add.new.norm.gif" alt="add to basket" name="Info" width="85" height="31" border="0"></a></font></td>
    </tr>
    <tr>
      <td height="30" class="<?php echo $style ?>"><div align="center"><font size="-3" face="Verdana, Arial, Helvetica, sans-serif"><strong>&pound;<?php echo htmlspecialchars($row["Product_price"]) ?></strong></font></div></td>
    </tr>
    <?php
  }
  mysql_free_result($res);
?>
  </table>
  <?php } ?>
</div>
<p align="center">
  <?php function showrow($row, $recid)
  {
?>
</p>
<table width="500" height="183" border="0" align="center" cellpadding="4" cellspacing="1" class="bd">
  <!--DWLayoutTable-->
  <tr>
    <td width="705" height="26" valign="top"><div align="center"><font size="4" face="Verdana, Arial, Helvetica, sans-serif"><?php echo htmlspecialchars($row["Product_Name"]) ?></font></div></td>
    <td width="102" rowspan="2" align="center" valign="middle"><?php echo outimage($recid, "Product_Picture", "product picture", "", "") ?>
        <p><strong><font size="3" face="Verdana, Arial, Helvetica, sans-serif">&pound;<?php echo htmlspecialchars($row["Product_price"]) ?></font></strong></p></td>
  </tr>
  <tr>
    <td height="64" valign="top"><font size="3"><strong><?php echo htmlspecialchars("Product Description")."&nbsp;" ?> : </strong></font>
        <blockquote>
          <p><?php echo htmlspecialchars($row["Product_Description"]) ?></p>
        </blockquote></td>
  </tr>
  <tr>
    <td height="23"><font size="3" face="Verdana, Arial, Helvetica, sans-serif"><strong><?php echo htmlspecialchars("Product_Features")."&nbsp;" ?> :</strong> </font>
        <blockquote>
          <p><?php echo htmlspecialchars($row["Product_Features"]) ?></p>
        </blockquote></td>
    <td align="center" valign="middle"><a href="<?php echo htmlspecialchars($row["Product_Pdf"]) ?>"><img src="../images/pdf_icon.gif" alt="" width="53" height="57" border="0"></a></td>
  </tr>
  <tr>
    <td height="23" colspan="2"><blockquote>
      <p align="center"><strong><font size="+6"><a href="<?php echo htmlspecialchars($row["Product_Cart"]) ?>">cart</a></font></strong></p>
    </blockquote></td>
  </tr>
</table>
<?php } ?>

<?php function showpagenav($page, $pagecount)
{
?>
<?php } ?>
<?php function showrecnav($a, $recid, $count)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="prod_listing.php">Index Page</a></td>
<?php if ($recid > 0) { ?>
<td><a href="prod_listing.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
<?php } if ($recid < $count - 1) { ?>
<td><a href="prod_listing.php?a=<?php echo $a ?>&recid=<?php echo $recid + 1 ?>">Next Record</a></td>
<?php } ?>
</tr>
</table>
<hr size="1" noshade>
<p>
  <?php } ?>
  
  
  <?php function viewrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("view", $recid, $count);
?>
  <br>
  <?php showrow($row, $recid) ?>
  <?php
  mysql_free_result($res);
} ?>
  
  <?php function connect()
{
  $conn = mysql_connect("host", "username", "password");
  mysql_select_db("sounscou_Datapoint");
  return $conn;
}

function sql_select()
{
  global $conn;
  $sql = "SELECT `Product_Name`, `Product_Description`, `Product_Picture`, `Product_price`, `Product_Category`, `Product_Pdf`, `Product_Cart`, `Product_Features`, `Product_subcat`, `Key` FROM `prod_listing`";
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

function sql_getrecordcount()
{
  global $conn;
  $sql = "SELECT COUNT(*) FROM `prod_listing`";
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
}function get_file_content_as_sql($fieldname)
{
  global $_FILES;
  $sql = "";
  if (!empty($_FILES))
    $sql=mysql_escape_string(file_get_contents(@$_FILES['x_'.$fieldname]['tmp_name']));
  if ($sql == '')
    $sql = 'null';
  else
   $sql = "'".$sql."'";
  return $sql;
}

function get_file_content_as_sql_for_update($fieldname)
{
  global $_POST;
  $sql = "`$fieldname` = `$fieldname`";
  if (isset($_POST["a_".$fieldname]))
  {
    switch ($_POST["a_".$fieldname]) {
    //remove
    case "2":
      $sql = "`$fieldname`= null";
      break;
    //replace
    case "3":
      $sql = "`$fieldname`=" .get_file_content_as_sql($fieldname);
      break;
    }
  }
  return $sql;
}
function outimage($recid, $field, $alt, $width, $height)
{
  $res = "<img src=\"prod_listing.php?getimage=$recid&field=$field\" border=0 alt=\"$alt\"";
  if ($width <> '')
    $res = $res." width=\"$width\"";
  if ($height <> '')
    $res = $res." height=\"$height\"";
  $res = $res.">";
  return $res;
}

function getimage($recid, $field)
{
  $res = sql_select();
  mysql_data_seek($res, $recid);;
  $row = mysql_fetch_assoc($res);
  echo $row[$field];
}

 ?>
</p>

</body>
</html>

mysql php

4 Contributors
6 Replies
363 Views
1 Week Discussion Span
Latest Post 18 Years Ago Latest Post by nav33n

Dani AI

Generated 9 Months Ago

Short answer: you must (1) quote and escape string values in SQL, (2) add the same WHERE to both the SELECT and the COUNT query, and (3) be careful how you identify a single record for the “view” page (your code currently uses a row index, which changes when you filter). already pointed out the quoting — missing quotes around a multi‑word category like Acoustic treatment is the usual cause of the "syntax error".

Example pattern to do this safely (build the category once, escape it, use it in both queries):

$category = 'Acoustic treatment';
$category_safe = mysql_real_escape_string($category, $conn);

$sql = "SELECT Product_Name, Product_Description, Product_Picture, Product_price, Product_Category, Product_Pdf, Product_Cart, Product_Features, Product_subcat, `Key`
        FROM prod_listing
        WHERE Product_Category = '$category_safe'";

$sql_count = "SELECT COUNT(*) AS cnt FROM prod_listing WHERE Product_Category = '$category_safe'";

If the category comes from user input, escape it exactly as above (or better, use prepared statements). Also avoid embedding unescaped values directly into a double‑quoted PHP string — that often causes either a PHP parse error or a broken SQL string.

Important caveat tied to your current code flow: the listing/view code uses mysql_data_seek($res, $recid) to fetch the Nth row. Once you filter with WHERE, the resultset and its row indexes change. For stable 'view' links, use the table primary key (Key) in the URL and SELECT WHERE Key = <id> when showing a single record (cast to int to avoid injection). That keeps paging and single‑record views predictable when filtering.

Finally, note the old mysql_* extension is deprecated; for any new work move to mysqli or PDO and use prepared statements to avoid these quoting/escaping pitfalls.

All 6 Replies

MitkOK 61 Junior Poster

18 Years Ago

Hi.

Is this the query you want to edit and put WHERE caluse :

$sql = "SELECT `Product_Name`, `Product_Description`, `Product_Picture`, `Product_price`, `Product_Category`, `Product_Pdf`, `Product_Cart`, `Product_Features`, `Product_subcat`, `Key` FROM `prod_listing`";

MitkOK 61 Junior Poster

18 Years Ago

Hi again.

I cannot understand what's the problem to add WHERE clause to the queries ?

$query = " SELECT ........ FROM table WHERE Product_Category = 'Acoustic treatment'; "

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

duttydea 0 Newbie Poster · Answer 1 · 2008-01-12T00:51:28+00:00

Hi.

Is this the query you want to edit and put WHERE caluse :

$sql = "SELECT `Product_Name`, `Product_Description`, `Product_Picture`, `Product_price`, `Product_Category`, `Product_Pdf`, `Product_Cart`, `Product_Features`, `Product_subcat`, `Key` FROM `prod_listing`";

To my knowledge i need to change both queries as 1 is the record count & the other is the records!!

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 2 · 2008-01-12T16:42:21+00:00

You haven't shown us where you want to include a where clause. You should mention your problem in detail. :)

sandratampa 0 Newbie Poster · Answer 3 · 2008-01-24T03:06:47+00:00

Im using PHP5 fast & Easy by Julie Meloni to create a database.

I have this error on my data update page:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id ='_POST[id]'' at line 9"

This is my code:

<?
session_start();
//check for required form variables
if ((!$_POST[first]) || (!$_POST[last])) {
    header("Location:");
    exit;
} else {
    //if form variables are present,start a session
    session_start();
}
//check for validity of user
if ($_SESSION[valid] != "yes"){
    header("Location:");
    exit;
}

//set up table and database names
include("dbinfo.inc.php");

//connect to server and select database
$connection = @mysql_connect($hostname,$username,$password) or die(mysql_error());
$db = @mysql_select_db($dbname,$connection) or die(mysql_error());

//build and issue query
$sql ="UPDATE $usertable SET
    first ='$_POST[first]',
    last ='$_POST[last]',
    homeph ='$_POST[homeph]',
    cellph ='$_POST[cellph]',
    fax ='$_POST[fax]',
    email ='$_POST[email]',
    web ='$_POST[web]',
    [COLOR="Red"]WHERE id ='_POST[id]'";[/COLOR]

$result = @mysql_query($sql,$connection) or die(mysql_error());
?>

I have PHP version 4.3 and mySQL 4.1

Any suggestions?

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 4 · 2008-01-24T08:53:46+00:00

nav33n 472 Purple hazed!

18 Years Ago

....
where id='"$_POST[id]"'"; // << $ sign.

PHP Mysql "Where" statement

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers