PHP Mysql "Where" statement

Question

duttydea 0 Newbie Poster

16 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>

php

4 Contributors
6 Replies
331 Views
1 Week Discussion Span
Latest Post 16 Years Ago Latest Post by nav33n

All 6 Replies

MitkOK 61 Junior Poster

16 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

16 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:http://www.myweb.com/database2/pick_modcontact.php");
    exit;
} else {
    //if form variables are present,start a session
    session_start();
}
//check for validity of user
if ($_SESSION[valid] != "yes"){
    header("Location:http://www.myweb.com/database2/contact_menu.php");
    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!

16 Years Ago

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

PHP Mysql "Where" statement

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers