PHP Mysql "Where" statement

Reply

•
•
Join Date: Jan 2008
Posts: 20
Reputation: duttydea is an unknown quantity at this point 
Solved Threads: 0
duttydea duttydea is offline Offline
Newbie Poster

PHP Mysql "Where" statement

 
0
  #1
Jan 11th, 2008
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 Syntax (Toggle Plain Text) 
  1. <?php session_start();
  2.  
  3.   if (isset($_GET["getimage"]))
  4.   {
  5.     $conn = connect();
  6.     getimage(@$_GET["getimage"], @$_GET["field"]);
  7.     exit;
  8.   }
  9. ?>
  10.  
  11. <html>
  12. <head>
  13. <title>sounscou_Datapoint -- prod_listing</title>
  14. <meta name="generator" http-equiv="content-type" content="text/html">
  15. <style type="text/css">
  16.   body {
  17.     background-color: #FFFFFF;
  18.     color: #000000;
  19.     font-family: Arial;
  20.     font-size: 12px;
  21.   }
  22.   .bd {
  23.     background-color: #FFFFFF;
  24.     color: #000000;
  25.     font-family: Arial;
  26.     font-size: 12px;
  27.   }
  28.   .tbl {
  29.     background-color: #FFFFFF;
  30.   }
  31.   a:link { 
  32.     background-color: #FFFFFF01;
  33.     color: #0000CD;
  34.     font-family: Arial;
  35.     font-size: 12px;
  36.   }
  37.   a:active { 
  38.     background-color: #FFFFFF01;
  39.     color: #0000FF;
  40.     font-family: Arial;
  41.     font-size: 12px;
  42.   }
  43.   a:visited { 
  44.     background-color: #FFFFFF01;
  45.     color: #800080;
  46.     font-family: Arial;
  47.     font-size: 12px;
  48.     font-style: italic;
  49.   }
  50.   .hr {
  51.     background-color: #000000;
  52.     color: #FFFFFF;
  53.     font-family: Arial;
  54.     font-size: 12px;
  55.   }
  56.   a.hr:link {
  57.     color: #FFFFFF;
  58.     font-family: Arial;
  59.     font-size: 12px;
  60.   }
  61.   a.hr:active {
  62.     color: #FFFFFF;
  63.     font-family: Arial;
  64.     font-size: 12px;
  65.   }
  66.   a.hr:visited {
  67.     color: #FFFFFF;
  68.     font-family: Arial;
  69.     font-size: 12px;
  70.   }
  71.   .dr {
  72.     background-color: #FFFFFF;
  73.     color: #000000;
  74.     font-family: Arial;
  75.     font-size: 12px;
  76.   }
  77.   .sr {
  78.     background-color: #F3F3F3;
  79.     color: #000000;
  80.     font-family: Arial;
  81.     font-size: 12px;
  82.     font-weight: bold;
  83.   }
  84. </style>
  85. </head>
  86. <body>
  87.  
  88. <p>
  89.   <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>
  90.   <?php
  91.   $conn = connect();
  92.   $showrecs = 10;
  93.   $pagerange = 10;
  94.  
  95.   $a = @$_GET["a"];
  96.   $recid = @$_GET["recid"];
  97.   $page = @$_GET["page"];
  98.   if (!isset($page)) $page = 1;
  99.  
  100.   switch ($a) {
  101.     case "view":
  102.       viewrec($recid);
  103.       break;
  104.     default:
  105.       select();
  106.       break;
  107.   }
  108.  
  109.  
  110.   mysql_close($conn);
  111. ?>
  112. </p>
  113. <p>
  114.  
  115.  
  116.  
  117.   <?php function select()
  118.   {
  119.   global $a;
  120.   global $showrecs;
  121.   global $page;
  122.  
  123.   $res = sql_select();
  124.   $count = sql_getrecordcount();
  125.   if ($count % $showrecs != 0) {
  126.     $pagecount = intval($count / $showrecs) + 1;
  127.   }
  128.   else {
  129.     $pagecount = intval($count / $showrecs);
  130.   }
  131.   $startrec = $showrecs * ($page - 1);
  132.   if ($startrec < $count) {mysql_data_seek($res, $startrec);}
  133.   $reccount = min($showrecs * $page, $count);
  134. ?>
  135. </p>
  136. <table class="bd" border="0" cellspacing="1" cellpadding="4">
  137. <tr>
  138.   <td><div align="center">Complete Product Listing</div></td>
  139. </tr>
  140. <tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
  141. </table>
  142. <hr size="1" noshade>
  143. <div align="center">
  144.   <?php showpagenav($page, $pagecount); ?>
  145.   <table class="bd" border="0" cellspacing="1" cellpadding="4">
  146.     <tr>
  147.       <?php if ($page > 1) { ?>
  148.       <td><a href="prod_listing.php?page=<?php echo $page - 1 ?>">&lt;&lt;&nbsp;Prev</a>&nbsp;</td>
  149.       <?php } ?>
  150.       <?php
  151.   global $pagerange;
  152.  
  153.   if ($pagecount > 1) {
  154.  
  155.   if ($pagecount % $pagerange != 0) {
  156.     $rangecount = intval($pagecount / $pagerange) + 1;
  157.   }
  158.   else {
  159.     $rangecount = intval($pagecount / $pagerange);
  160.   }
  161.   for ($i = 1; $i < $rangecount + 1; $i++) {
  162.     $startpage = (($i - 1) * $pagerange) + 1;
  163.     $count = min($i * $pagerange, $pagecount);
  164.  
  165.     if ((($page >= $startpage) && ($page <= ($i * $pagerange)))) {
  166.       for ($j = $startpage; $j < $count + 1; $j++) {
  167.         if ($j == $page) {
  168. ?>
  169.       <td><b><?php echo $j ?></b></td>
  170.       <?php } else { ?>
  171.       <td><a href="prod_listing.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
  172.       <?php } } } else { ?>
  173.       <td><a href="prod_listing.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
  174.       <?php } } } ?>
  175.       <?php if ($page < $pagecount) { ?>
  176.       <td>&nbsp;<a href="prod_listing.php?page=<?php echo $page + 1 ?>">Next&nbsp;&gt;&gt;</a>&nbsp;</td>
  177.       <?php } ?>
  178.     </tr>
  179.   </table>
  180.   <table class="tbl" border="0" cellspacing="1" cellpadding="6"width="90%">
  181.     <?php
  182.   for ($i = $startrec; $i < $reccount; $i++)
  183.   {
  184.     $row = mysql_fetch_assoc($res);
  185.     $style = "dr";
  186.     if ($i % 2 != 0) {
  187.       $style = "sr";
  188.     }
  189. ?>
  190.     <tr bordercolor="#000000" bgcolor="#FFFFFF">
  191.       <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>
  192.       <td width="22%" height="30" class="<?php echo $style ?>"><div align="center">
  193.         <p><font size="-3" face="Verdana, Arial, Helvetica, sans-serif"><?php echo htmlspecialchars($row["Product_Name"]) ?></font></p>
  194.       </div></td>
  195.       <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>
  196.       <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>
  197.       <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>
  198.       <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>
  199.     </tr>
  200.     <tr>
  201.       <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>
  202.     </tr>
  203.     <?php
  204.   }
  205.   mysql_free_result($res);
  206. ?>
  207.   </table>
  208.   <?php } ?>
  209. </div>
  210. <p align="center">
  211.   <?php function showrow($row, $recid)
  212.   {
  213. ?>
  214. </p>
  215. <table width="500" height="183" border="0" align="center" cellpadding="4" cellspacing="1" class="bd">
  216.   <!--DWLayoutTable-->
  217.   <tr>
  218.     <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>
  219.     <td width="102" rowspan="2" align="center" valign="middle"><?php echo outimage($recid, "Product_Picture", "product picture", "", "") ?>
  220.         <p><strong><font size="3" face="Verdana, Arial, Helvetica, sans-serif">&pound;<?php echo htmlspecialchars($row["Product_price"]) ?></font></strong></p></td>
  221.   </tr>
  222.   <tr>
  223.     <td height="64" valign="top"><font size="3"><strong><?php echo htmlspecialchars("Product Description")."&nbsp;" ?> : </strong></font>
  224.         <blockquote>
  225.           <p><?php echo htmlspecialchars($row["Product_Description"]) ?></p>
  226.         </blockquote></td>
  227.   </tr>
  228.   <tr>
  229.     <td height="23"><font size="3" face="Verdana, Arial, Helvetica, sans-serif"><strong><?php echo htmlspecialchars("Product_Features")."&nbsp;" ?> :</strong> </font>
  230.         <blockquote>
  231.           <p><?php echo htmlspecialchars($row["Product_Features"]) ?></p>
  232.         </blockquote></td>
  233.     <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>
  234.   </tr>
  235.   <tr>
  236.     <td height="23" colspan="2"><blockquote>
  237.       <p align="center"><strong><font size="+6"><a href="<?php echo htmlspecialchars($row["Product_Cart"]) ?>">cart</a></font></strong></p>
  238.     </blockquote></td>
  239.   </tr>
  240. </table>
  241. <?php } ?>
  242.  
  243. <?php function showpagenav($page, $pagecount)
  244. {
  245. ?>
  246. <?php } ?>
  247. <?php function showrecnav($a, $recid, $count)
  248. {
  249. ?>
  250. <table class="bd" border="0" cellspacing="1" cellpadding="4">
  251. <tr>
  252. <td><a href="prod_listing.php">Index Page</a></td>
  253. <?php if ($recid > 0) { ?>
  254. <td><a href="prod_listing.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
  255. <?php } if ($recid < $count - 1) { ?>
  256. <td><a href="prod_listing.php?a=<?php echo $a ?>&recid=<?php echo $recid + 1 ?>">Next Record</a></td>
  257. <?php } ?>
  258. </tr>
  259. </table>
  260. <hr size="1" noshade>
  261. <p>
  262.   <?php } ?>
  263.  
  264.  
  265.   <?php function viewrec($recid)
  266. {
  267.   $res = sql_select();
  268.   $count = sql_getrecordcount();
  269.   mysql_data_seek($res, $recid);
  270.   $row = mysql_fetch_assoc($res);
  271.   showrecnav("view", $recid, $count);
  272. ?>
  273.   <br>
  274.   <?php showrow($row, $recid) ?>
  275.   <?php
  276.   mysql_free_result($res);
  277. } ?>
  278.  
  279.   <?php function connect()
  280. {
  281.   $conn = mysql_connect("host", "username", "password");
  282.   mysql_select_db("sounscou_Datapoint");
  283.   return $conn;
  284. }
  285.  
  286. function sql_select()
  287. {
  288.   global $conn;
  289.   $sql = "SELECT `Product_Name`, `Product_Description`, `Product_Picture`, `Product_price`, `Product_Category`, `Product_Pdf`, `Product_Cart`, `Product_Features`, `Product_subcat`, `Key` FROM `prod_listing`";
  290.   $res = mysql_query($sql, $conn) or die(mysql_error());
  291.   return $res;
  292. }
  293.  
  294. function sql_getrecordcount()
  295. {
  296.   global $conn;
  297.   $sql = "SELECT COUNT(*) FROM `prod_listing`";
  298.   $res = mysql_query($sql, $conn) or die(mysql_error());
  299.   $row = mysql_fetch_assoc($res);
  300.   reset($row);
  301.   return current($row);
  302. }function get_file_content_as_sql($fieldname)
  303. {
  304.   global $_FILES;
  305.   $sql = "";
  306.   if (!empty($_FILES))
  307.     $sql=mysql_escape_string(file_get_contents(@$_FILES['x_'.$fieldname]['tmp_name']));
  308.   if ($sql == '')
  309.     $sql = 'null';
  310.   else
  311.    $sql = "'".$sql."'";
  312.   return $sql;
  313. }
  314.  
  315. function get_file_content_as_sql_for_update($fieldname)
  316. {
  317.   global $_POST;
  318.   $sql = "`$fieldname` = `$fieldname`";
  319.   if (isset($_POST["a_".$fieldname]))
  320.   {
  321.     switch ($_POST["a_".$fieldname]) {
  322.     //remove
  323.     case "2":
  324.       $sql = "`$fieldname`= null";
  325.       break;
  326.     //replace
  327.     case "3":
  328.       $sql = "`$fieldname`=" .get_file_content_as_sql($fieldname);
  329.       break;
  330.     }
  331.   }
  332.   return $sql;
  333. }
  334. function outimage($recid, $field, $alt, $width, $height)
  335. {
  336.   $res = "<img src=\"prod_listing.php?getimage=$recid&field=$field\" border=0 alt=\"$alt\"";
  337.   if ($width <> '')
  338.     $res = $res." width=\"$width\"";
  339.   if ($height <> '')
  340.     $res = $res." height=\"$height\"";
  341.   $res = $res.">";
  342.   return $res;
  343. }
  344.  
  345. function getimage($recid, $field)
  346. {
  347.   $res = sql_select();
  348.   mysql_data_seek($res, $recid);;
  349.   $row = mysql_fetch_assoc($res);
  350.   echo $row[$field];
  351. }
  352.  
  353.  ?>
  354. </p>
  355.  
  356. </body>
  357. </html>
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 142
Reputation: MitkOK is an unknown quantity at this point 
Solved Threads: 12
MitkOK's Avatar
MitkOK MitkOK is offline Offline
Junior Poster

Re: PHP Mysql "Where" statement

 
0
  #2
Jan 11th, 2008
Hi.

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

php Syntax (Toggle Plain Text) 
  1. $sql = "SELECT `Product_Name`, `Product_Description`, `Product_Picture`, `Product_price`, `Product_Category`, `Product_Pdf`, `Product_Cart`, `Product_Features`, `Product_subcat`, `Key` FROM `prod_listing`";
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 20
Reputation: duttydea is an unknown quantity at this point 
Solved Threads: 0
duttydea duttydea is offline Offline
Newbie Poster

Re: PHP Mysql "Where" statement

 
0
  #3
Jan 11th, 2008
•
•
•
•
Originally Posted by MitkOK View Post
Hi.

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

php Syntax (Toggle Plain Text) 
  1. $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!!
Last edited by duttydea; Jan 11th, 2008 at 2:12 pm. Reason: spelling mistakes
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 142
Reputation: MitkOK is an unknown quantity at this point 
Solved Threads: 12
MitkOK's Avatar
MitkOK MitkOK is offline Offline
Junior Poster

Re: PHP Mysql "Where" statement

 
0
  #4
Jan 11th, 2008
Hi again.

I cannot understand what's the problem to add WHERE clause to the queries ?
sql Syntax (Toggle Plain Text) 
  1. $query = " SELECT ........ FROM table WHERE Product_Category = 'Acoustic treatment'; "
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2007
Posts: 3,833
Reputation: nav33n is a jewel in the rough nav33n is a jewel in the rough nav33n is a jewel in the rough nav33n is a jewel in the rough 
Solved Threads: 344
Moderator
Featured Poster
nav33n's Avatar
nav33n nav33n is offline Offline
Senior Poster

Re: PHP Mysql "Where" statement

 
0
  #5
Jan 12th, 2008
You haven't shown us where you want to include a where clause. You should mention your problem in detail.
Ignorance is definitely not bliss!

*PM asking for help will be ignored*
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 1
Reputation: sandratampa is an unknown quantity at this point 
Solved Threads: 0
sandratampa sandratampa is offline Offline
Newbie Poster

Re: PHP Mysql "Where" statement

 
0
  #6
Jan 23rd, 2008
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]',
WHERE id ='_POST[id]'";

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


I have PHP version 4.3 and mySQL 4.1

Any suggestions?
Last edited by sandratampa; Jan 23rd, 2008 at 4:08 pm.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2007
Posts: 3,833
Reputation: nav33n is a jewel in the rough nav33n is a jewel in the rough nav33n is a jewel in the rough nav33n is a jewel in the rough 
Solved Threads: 344
Moderator
Featured Poster
nav33n's Avatar
nav33n nav33n is offline Offline
Senior Poster

Re: PHP Mysql "Where" statement

 
0
  #7
Jan 23rd, 2008
php Syntax (Toggle Plain Text) 
  1. ....
  2. where id='"$_POST[id]"'"; // << $ sign.
  3.  
Last edited by nav33n; Jan 23rd, 2008 at 9:54 pm.
Ignorance is definitely not bliss!

*PM asking for help will be ignored*
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum


Views: 1849 | Replies: 6
Thread Tools Search this Thread



Tag cloud for PHP
access ajax apache archive array arrays binary box buttons cakephp cart check checkbox class classes cms code database date delete development directory display download dropdown dropdownlist drupal dynamic echo email error file files form forms functions header hosting href htaccess html image include insert ip java javascript joomla jquery limit link login loop mail menu mlm mod_rewrite multiple mysql order parse password paypal php post problem query radio redirect regex remote rewrite script search select server session sort source sql storage string structure syntax system table tutorial update updates upload url user validation validator variable video web website wordpress xml
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2010 DaniWeb® LLC