943,882 Members | Top Members by Rank

Web Development > PHP > PHP Mysql "Where" statement
Ad:
  • PHP Discussion Thread
  • Unsolved
  • Views: 2618
  • PHP RSS
Permalink
Jan 11th, 2008
0

PHP Mysql "Where" statement

Expand Post »
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>
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
duttydea is offline Offline
21 posts
since Jan 2008
Permalink
Jan 11th, 2008
0

Re: PHP Mysql "Where" statement

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`";
Reputation Points: 59
Solved Threads: 12
Junior Poster
MitkOK is offline Offline
142 posts
since Jul 2007
Permalink
Jan 11th, 2008
0

Re: PHP Mysql "Where" statement

Click to Expand / Collapse  Quote originally posted by MitkOK ...
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 3:12 pm. Reason: spelling mistakes
Reputation Points: 10
Solved Threads: 0
Newbie Poster
duttydea is offline Offline
21 posts
since Jan 2008
Permalink
Jan 11th, 2008
0

Re: PHP Mysql "Where" statement

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'; "
Reputation Points: 59
Solved Threads: 12
Junior Poster
MitkOK is offline Offline
142 posts
since Jul 2007
Permalink
Jan 12th, 2008
0

Re: PHP Mysql "Where" statement

You haven't shown us where you want to include a where clause. You should mention your problem in detail.
Moderator
Featured Poster
Reputation Points: 524
Solved Threads: 356
Purple hazed!
nav33n is offline Offline
3,878 posts
since Nov 2007
Permalink
Jan 23rd, 2008
0

Re: PHP Mysql "Where" statement

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 5:08 pm.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
sandratampa is offline Offline
1 posts
since Jan 2008
Permalink
Jan 23rd, 2008
0

Re: PHP Mysql "Where" statement

php Syntax (Toggle Plain Text)
  1. ....
  2. where id='"$_POST[id]"'"; // << $ sign.
  3.  
Last edited by nav33n; Jan 23rd, 2008 at 10:54 pm.
Moderator
Featured Poster
Reputation Points: 524
Solved Threads: 356
Purple hazed!
nav33n is offline Offline
3,878 posts
since Nov 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in PHP Forum Timeline: Need more help with "if"
Next Thread in PHP Forum Timeline: format for option statment in php...





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC