PHP, MySQL Query not working

Question

Working on a small university project to develop a small php site. Seem to be having a couple of issues. I am a complete newbe when it comes to programming of any kind. I am literately cutting and pasting (From provided scripts) and trying to get things to come together in the way I require.

I seem to be having a problem collecting data from a database query, I already have one on the script which works perfectly. I am trying to add another, however can't seem to get this to work.

http://mkiddr.com/phptests/shopping/category.php?id=2

Currently the category.php is displaying all the products within the category. However I wish for it to also display the category description and you'll see in the following code how I've tried to do this:

 <?php
session_start();
include "conn.php";
include "header.php";

if (isset($_GET['id'])){
    $CategoryID = $_GET['id'];
    $q="SELECT ProductID, ProductName FROM Products WHERE CategoryID=$CategoryID";
    $d="SELECT Desc FROM ProductCategories WHERE CategoryID=$CategoryID";

    $result = mysqli_query($_SESSION['conn'],$q);

    $result2 = mysqli_query($_SESSION['conn'],$d);

    echo "<div>";
    while ($row = mysqli_fetch_row($result)){
        echo "<p><a href='product.php?id=".$row[0]."'>".$row[1]."</a></p>";
    }
    echo "<p>".$result2."</p>";
    echo "</div>";
    mysqli_free_result($result);
}
include "footer.php";
?>

Would appreciate some help!

UPDATED CODE (Still not working)

   <?php
   session_start();
   include "conn.php";
   include "header.php";

 if (isset($_GET['id'])){
$CategoryID = $_GET['id'];
$q="SELECT ProductID, ProductName FROM Products WHERE CategoryID=$CategoryID";
$d="SELECT `Desc` FROM ProductCategories WHERE CategoryID=$CategoryID";

$result = mysqli_query($_SESSION['conn'],$q);
$result2 = mysqli_query($_SESSION['conn'],$d);  

echo "<div>";
while ($row = mysqli_fetch_row($result)){
    echo "<p><a href='product.php?id=".$row[0]."'>".$row[1]."</a></p>";
}
echo "</div>";
mysqli_free_result($result);

//Description
echo "<div>";
while ($result2 = mysqli_fetch_assoc($result2)){
    echo "<p>".$result2[0]."</p>";
   }

   }
  include "footer.php";
  ?>

score 4 · Answer 1 · edited May 23 '17 at 11:57

4

One of the errors I've seen is the use of reserved keywords.

The reason why $d is not executing is because column DESC is a reserved keyword from MYSQL. You can delimite it with backtick or supply an alias on the table to make it work, eg

$d="SELECT `Desc` FROM ProductCategories WHERE CategoryID=$CategoryID";

OR

$d="SELECT a.Desc FROM ProductCategories a WHERE CategoryID=$CategoryID";

MySQL Reserved Keywords List

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 11:57

Community

1
1

answered Mar 07 '13 at 16:39

John Woo

258,903
69
498
492

Ahh fantastic this would make sense. However I seem to then receive the following error: Catchable fatal error: Object of class mysqli_result could not be converted to string in /volume1/web/phptests/shopping/category.php on line 19 – BayscapeNewbie Mar 07 '13 at 16:41
echo "
".$result2."
"; – BayscapeNewbie Mar 07 '13 at 16:58
What are you expecting that to do? P.S. please start response with @barmar if you want me to notice it. – Barmar Mar 07 '13 at 17:06
@bamar, apologies. It should be collecting the Categories Description from the database.... It should then be displaying this description based on the categories ID. – BayscapeNewbie Mar 07 '13 at 17:08
The new `while` loop in the UPDATE is overwriting `$result2` with the result of `mysqli_fetch_assoc`. So the second time through the loop it's no longer a result that can be used as an argument. Use a different variable, like `$row2`. And my name is Barmar, not Bamar, so I wasn't notified of your response. Use SO's name completion, then typos should be minimized. – Barmar Mar 07 '13 at 17:22

Jonas m · Answer 2 · 2013-03-07T17:31:41.050

1

First remember to fetch the query into an array:

$result2 = mysqli_fetch_assoc($result2);

Since the query is saved into an associative array you should call it in that way $result2['Desc']

<?php
   session_start();
   include "conn.php";
   include "header.php";

 if (isset($_GET['id'])){
$CategoryID = $_GET['id'];
$q="SELECT ProductID, ProductName FROM Products WHERE CategoryID=$CategoryID";
$d="SELECT `Desc` FROM ProductCategories WHERE CategoryID=$CategoryID";

$result = mysqli_query($_SESSION['conn'],$q);
$result2 = mysql_fetch_assoc( mysqli_query($_SESSION['conn'],$d) );  

echo "<div>";
while ($row = mysqli_fetch_row($result)){
    echo "<p><a href='product.php?id=".$row[0]."'>".$row[1]."</a></p>";
}
echo "</div>";
mysqli_free_result($result);

//Description
echo "<div>";
          echo "<p>".$result2['Desc']."</p>";
echo "</div>";

   }
  include "footer.php";
  ?>

edited Mar 07 '13 at 17:31

answered Mar 07 '13 at 16:44

Jonas m

2,646
3
22
43

1

This is just plain wrong, `mysqli_fetch_row` does not return a 2-dimensional array. – Barmar Mar 07 '13 at 16:53
That is true, my bad. I edited my answer with an assoc command. – Jonas m Mar 07 '13 at 17:01
Now your answer is no longer wrong, but it's still not a solution. `mysqli_fetch_array()` works fine the way he used it. – Barmar Mar 07 '13 at 17:05
Perhaps its just me going blind Barmar. But i see no mysqli_fetch_array command in his code. I see him resulting his first query, but not his second query. Could you point to me where it is then. Jonas – Jonas m Mar 07 '13 at 17:08
I thought you were telling him to use `mysqli_fetch_assoc` _instead_ of `mysqli_fetch_row`. – Barmar Mar 07 '13 at 17:20
Ah. No dear sir. Perhaps you will withdraw your downvote then? – Jonas m Mar 07 '13 at 17:28

PHP, MySQL Query not working

2 Answers2