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";
  ?>

Check the code in my post.

Jonas m
– Jonas m

2013-03-07 17:38:08 +00:00
Commented Mar 7, 2013 at 17:38 — Jonas m
– Jonas m, Commented Mar 7, 2013 at 17:38

Community · Accepted Answer · 2017-05-23 11:57:40Z

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, 2017 at 11:57

CommunityBot

11 silver badge

answered Mar 7, 2013 at 16:39

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BayscapeNewbie Over a year ago

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 Over a year ago

echo "<p>".$result2."</p>";

Barmar Over a year ago

What are you expecting that to do? P.S. please start response with @barmar if you want me to notice it.

BayscapeNewbie Over a year ago

@bamar, apologies. It should be collecting the Categories Description from the database.... It should then be displaying this description based on the categories ID.

Barmar Over a year ago

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.

Jonas m · Accepted Answer · 2013-03-07 17:31:41Z

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 7, 2013 at 17:31

answered Mar 7, 2013 at 16:44

Jonas m

2,7343 gold badges25 silver badges43 bronze badges

6 Comments

Barmar Over a year ago

This is just plain wrong, mysqli_fetch_row does not return a 2-dimensional array.

Jonas m Over a year ago

That is true, my bad. I edited my answer with an assoc command.

Barmar Over a year ago

Now your answer is no longer wrong, but it's still not a solution. mysqli_fetch_array() works fine the way he used it.

Jonas m Over a year ago

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

Barmar Over a year ago

I thought you were telling him to use mysqli_fetch_assoc instead of mysqli_fetch_row.

|

Collectives™ on Stack Overflow

PHP, MySQL Query not working

2 Answers 2

5 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related