0

I'm trying to use PHP and MySQL to automatically update my "Products" page. I use MySQL to store my product's IDs, Product name, price, quantity etc. I need help getting PHP to take the MySQL data and put each ID into a <div> with a specific class.

I figured I needed a while() loop to get the array of the data and a foreach() loop to get each instance of the ID, but I am not using the foreach() loop right.

Please excuse my mess of coding. I'm trying to get all the concepts down and working before I look for more efficient ways of doing things. Note that the sql2['id'] contains more than just one item.

<?php
    mysql_connect('localhost', 'user', 'password');
    mysql_select_db('store');

    $sql1 = mysql_query('
        SELECT c.id, 
               c.name, 
               c.description, 
               c.price, 
               c.quantity, 
               c.itemid, 
               c.imgname, 
               c.position, 
               (SELECT Count(t.id) 
                FROM   topics AS t 
                WHERE  t.parent = c.id 
                       AND t.id2 = 1)   AS topics, 
               (SELECT Count(t2.id) 
                FROM   topics AS t2 
                WHERE  t2.parent = c.id 
                       AND t2.id2 != 1) AS replies 
        FROM   categories AS c 
        GROUP  BY c.id 
        ORDER  BY c.position ASC 
    ');

    if($sql1 === false){
        die(mysql_error());
    }

    while($sql2 = mysql_fetch_array($sql1)){
        foreach($sql2['id'] as $value){?>
            <div class="itemInset">
                <p><?php echo $sql2['name']; ?></p>
                <img src="admin/image/<?php echo $sql2['imgName']; ?>" alt="<?php echo $sql2['imgName']; ?>" />
            </div><?php
        }
    }
?>

The categories SQL:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` smallint(6) NOT NULL,
  `name` varchar(256) NOT NULL,
  `description` text NOT NULL,
  `price` text NOT NULL,
  `quantity` int(10) NOT NULL,
  `itemID` text NOT NULL,
  `cC` text NOT NULL,
  `imgName` text NOT NULL,
  `position` smallint(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And a var_dump():

array(20) {
  [0]=>
  string(1) "1"
  ["id"]=>
  string(1) "1"
  [1]=>
  string(9) "PC"
  ["name"]=>
  string(9) "PC"
  [2]=>
  string(58) "computer "
  ["description"]=>
  string(58) "computer "
  [3]=>
  string(6) "150.00"
  ["price"]=>
  string(6) "150.00"
  [4]=>
  string(4) "1000"
  ["quantity"]=>
  string(4) "1000"
  [5]=>
  string(8) "PCR-1000"
  ["itemID"]=>
  string(8) "PCR-1000"
  [6]=>
  string(0) ""
  ["imgName"]=>
  string(0) ""
  [7]=>
  string(1) "1"
  ["position"]=>
  string(1) "1"
  [8]=>
  string(1) "0"
  ["topics"]=>
  string(1) "0"
  [9]=>
  string(1) "0"
  ["replies"]=>
  string(1) "0"
}
totymedli
  • 29,531
  • 22
  • 131
  • 165
Austin Kregel
  • 715
  • 1
  • 8
  • 27

2 Answers2

1

Your foreach() loop is unnecessary if $sql2['id'] contains only 1 object.

Also why do you make a $value = $sql2['id'];? The $value variable is used by the foreach() loop, that is automatically assign the current object of the collection (which is in our case a collection of one object) to it. What increase the incomprehensibility is why did you created the foreach() loop and done the assign I described previously if you doesn't even use the $value variable?

Also in this case $sql2 is very misleading naming because that object doesn't represent an SQL related thing.

If you look at the mysql-fetch-array() documentation, you see some examples, how you should use it. Also notice the naming of the variables! Your code should look like something like this:

$result = mysql_query('
    SELECT c.id, 
           c.name, 
           c.description, 
           c.price, 
           c.quantity, 
           c.itemid, 
           c.imgname, 
           c.position, 
           (SELECT Count(t.id) 
            FROM   topics AS t 
            WHERE  t.parent = c.id 
                   AND t.id2 = 1)   AS topics, 
           (SELECT Count(t2.id) 
            FROM   topics AS t2 
            WHERE  t2.parent = c.id 
                   AND t2.id2 != 1) AS replies 
    FROM   categories AS c 
    GROUP  BY c.id 
    ORDER  BY c.position ASC 
');

while ($row = mysql_fetch_array($result)) {?>
   <div class="itemInset">
      <p><?php echo $row['name']; ?></p>
      <img src="admin/image/<?php echo $row['imgName']; ?>" alt="<?php echo $row['imgName']; ?>">
   </div>
<?php } ?>

Please note that mysql_* functions are deprecated and you shouldn't use them.

Edit: As I see, you do/think about things really bad. You should use the first technique I gave you and create the <div>s that way. If that doesn't works for you, than you wrote a bad SQL query or your database design is wrong. According to your SQL SELECT there should be only a number in sql2['id'] if not, than your database design/naming is totally wrong, not to mention that if it isn't a number than it is some kind of string and you can't foreach() a naked string and wait for some kind of miracle to happen that will generate the whole layout you want. At this point I have to say, that if the first solution I gave you didn't work, than you have to rethink your database and the way how do you insert/select things from those tables, because as I see you have misconceptions about how your could should work.

Each row that you get from while ($row = mysql_fetch_array($result)) should represent one category, and the columns of your categories table should represent properties of a category, as well as every object you got from your $row array. So when you get a value from the $row array than you should index it with the name of the property (the column's name if you didn't renamed it in the SQL query) and you should get a single value. For example $row['name'] should contain the name of that specific category, $row['id'] should be the id of that specific category. You can't do anything with a foreach() loop in these, unless they contain something like "auto,mobile,car" that you have to explode() first to loop it. And to repeat myself the id should be a specific, unique number of that specific category, like 1, 2, 56 or 468 it can't contain other/more things because in that case your naming and your conception about the id is wrong.

Community
  • 1
  • 1
totymedli
  • 29,531
  • 22
  • 131
  • 165
0

I'm assuming here that your query returns any values in the first place, but do test that with a var_dump($sql1); after that line.

Here your $sql1 is actually your result set.

If I'm understanding what you're trying to do, then the foreach shouldn't be necessary. You should have your results row in $sql2 (albeit not associative - so you'd have to use $sql2[0], if you choose not to use mysql_fetch_assoc() (or the mysql_fetch_array($resultset, MYSQL_ASSOC) method).

You should then use your while loop to ask your result set for a row.

$results = mysql_query('...SELECT STUFF....');

// i.e. dear results, please assign a row of your data to the variable $row
while ( $row = mysql_fetch_assoc($results) ) {

echo $row['id']; // and so on

}

The PHP manual has some good examples that should clear up using mysql_fetch_assoc

Ross
  • 3,022
  • 1
  • 17
  • 13
  • When I just use a while loop it sticks the mySQL data in the same div tag... I want it to use a div foreach new ID. – Austin Kregel Sep 01 '13 at 00:55
  • Could you share the output of an `echo "
    "; var_dump($sql2); echo "
    ";` as the first line inside your while loop? This might clarify what data you're getting in `$sql2`     – Ross Sep 01 '13 at 01:07
  • Thanks Austin. Can you see from that, that your row `$sql2` has a string value of `"1"` when you access `$sql2['id']`. So your foreach is trying to loop over a string which I imagine is not outputting anything. You might also notice there are a lot of duplicate values in that, which is a source of some confusion for me. My next question would be either "What output are you actually getting?" and/or "What's the format you're hoping to get out, if you manually wrote the html?" – Ross Sep 01 '13 at 01:31
  • Would an export of my table be of any use for you? – Austin Kregel Sep 01 '13 at 01:35
  • https://github.com/austinkregel/PHPProject/tree/master Is the link to my project with the files in question. I am not getting any out put other than my formatted div with nothing from mysql. I am hoping to get a formatted div containing my information. For each of the items in the sql database. – Austin Kregel Sep 01 '13 at 01:47
  • Afraid not, it seems like you're not getting the data structure you want out of your query, even before you get to the loops. Dropping the foreach loop and switching out `mysql_fetch_array($sql1)` with `mysql_fetch_assoc($sql1)` should get you a div for each row from your initial SELECT. It sounds like your initial query isn't producing the data set you want though...? – Ross Sep 01 '13 at 01:52
  • Okay so I got it to output my div but it doesnt do it for every instance there is a new ID. it uses the same div for each of the IDs. – Austin Kregel Sep 01 '13 at 02:07
  • As a last comment before I have to disappear, your github repo shows you use `mysql_fetch_array` to assign a row to `$dnn1`. This means that your `$dnn1` variable can only be accessed by numeric index (e.g. `$dnn1[0]`, and this is related to the order of the arguments in your SELECT. So, to get the id, you would use `echo $dnn[0]`. To be able to access `$dnn1` via `$dnn1['id']` it needs to be associative, so using either `mysql_fetch_assoc($dn1)` or `mysql_fetch_array($dn1, MYSQL_ASSOC)` should then at least give you output. – Ross Sep 01 '13 at 02:11