0
\$\begingroup\$

This code belongs to a shopping cart which I want to print multiple times with the help of a loop.

There are 9 SQL queries and I want all these queries to run at a time with the below table every time the SQL query changes, but the below table remains the same.

          //SQL query define in array
  $query = array(
  "SELECT  * FROM products   WHERE   rowno=1
   and   colno=1 and pageno=1 and  productLine like '$cat'",
   "SELECT  *  FROM   products  WHERE  rowno=1  and  colno=2
   and  pageno=1   and   productLine   like   '$cat'",
   "SELECT *  FROM  products WHERE rowno=1 and colno=3
    and pageno=1 and productLine like '$cat'",
   "SELECT * FROM products  WHERE rowno=2 and colno=1 and pageno=1
   and productLine like '$cat'",
  "SELECT * FROM products WHERE rowno=2 and colno=2
   and pageno=1 and productLine like '$cat'",
   "SELECT * FROM products WHERE rowno=2 and colno=3
   and pageno=1 and productLine like '$cat'",
   "SELECT * FROM products WHERE rowno=3 and colno=1
    and pageno=1 and productLine like '$cat'",
   "SELECT * FROM products WHERE rowno=3 and colno=2
    and pageno=1 and productLine like '$cat'",
    "SELECT * FROM products WHERE rowno=3 and colno=3
    and pageno=1 and productLine like '$cat'");

 foreach($query as  $a) 
 {

$cmd=mysql_query($query,$conn);  // con query
$row=mysql_fetch_row($cmd);    // fetch the table in database
echo
  "<tr>
  <td>$row[1]</td>      // table row
  </tr>
   <tr>
  <td><img alt='' src='$row[9]' style='height: 151px; width: 214px' />
  </td>
  </tr>
  <tr>
  <td>
  RS $row[7]&nbsp;</td>         // this table same in  loop but sql change
  </tr>
  <tr>
  <td>$row[8]</td>
  </tr>
 <tr><td><img alt='' src='divider.png' style='height:25px;
  width:207px'/>
   </td>
  </tr>
  <tr>
 <td>
   $row[5]</td>
 </tr>
 <tr>
 <td>Details</td>
 </tr>";
  } // end loop
\$\endgroup\$

closed as unclear what you're asking by Phrancis, Brythan, Mathieu Guindon, Abbas, RubberDuck Mar 25 '15 at 11:53

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1
\$\begingroup\$

Security

It's unclear where $cat comes from, but if it can possibly be user supplied (eg when it comes from a GET/POST request, or when it comes from the database where it might have been placed by a user), you should use prepared statements to prevent SQL injection.

You should also not be using mysql_* as it's deprecated.

Also, you should always prevent XSS when echoing data that might be user supplied.

Queries in array

Your approach of using an array to handle the 9 queries whose results should be handled the same is ok, but it would be better if you extracted your foreach loop into a separate function and then called that function with the queries (or better yet their result). Doing it that way would increase readability and reusability of your code.

Reduce amount of queries

You could remove the amount of queries you use by using or, which should increase the performance of your code. So instead of asking

SELECT * FROM products WHERE rowno=1 and colno=1 and pageno=1 and productLine like '$cat'";
SELECT * FROM products WHERE rowno=1 and colno=2 and pageno=1 and productLine like '$cat'";

You could just ask

SELECT * FROM products WHERE ((rowno=1 and colno=2 and pageno=1) or (rowno=1 and colno=2 and pageno=1)) and productLine like '$cat'";

This could be further simplified, and the other queries could be added as well, but honestly, I'm not quite sure if it wouldn't be easier to change your database schema. I'm not quite sure how it currently works though.

Misc

  • all your SQL keywords should be all uppercase to increase readability, not just some.
  • your comments all just reflect what the code just told me, so you can just remove them.
  • use proper indentation to increase readability.
  • select the concrete fields that you want, don't just select *.
  • move style information in it's own css file.
  • you forgot the opening and closing table tags.
  • why use like? it seem you want equality?

With some of these changes applied, your code might loop something like this:

$dbh = new PDO([...]); 
printShoppingCart(getByCat($pdo, $cat, "SELECT var1, var2, var3, var4, var5 FROM products WHERE rowno=1 AND colno=1 AND pageno=1 AND productLine=:line"));
[...]

function getByCat($pdo, $ca, $query) {
    $stmt = $dbh->prepare($query);
    $stmt->bindParam(':line', $cat);
    return $stmt->fetch();
}

function printShoppingCart($vars) {
    echo "<table>
    <tr>
        <td>" . htmlspecialchars($vars[0], ENT_QUOTES, 'UTF-8') . "</td>
    </tr>
    <tr>
        <td><img alt='' src='" . htmlspecialchars($vars[1], ENT_QUOTES, 'UTF-8') . "' style='height: 151px; width: 214px' />
        </td>
    </tr>
    <tr>
        <td>
            RS " . htmlspecialchars($vars[2], ENT_QUOTES, 'UTF-8') . "&nbsp;</td>
    </tr>
    <tr>
        <td>" . htmlspecialchars($vars[3], ENT_QUOTES, 'UTF-8') . "</td>
    </tr>
    <tr><td><img alt='' src='divider.png' style='height:25px;
                 width:207px'/>
        </td>
    </tr>
    <tr>
        <td>
            " . htmlspecialchars($vars[4], ENT_QUOTES, 'UTF-8') . "</td>
    </tr>
    <tr>
        <td>Details</td>
    </tr></table>";
}

There are more improvements that can be made, but I think this would be a good start.

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.