0

I am unsure how to display the items field. I want to display two tables of data; one that has all the items from a user and one with all the items to teh user. All I've been able to output is the item_id's(I pasted the html below). How to get all the item info from these ids, which is in the item table, and populate the HTML?

trans table

TRANS TABLES

item table

enter image description here

$from = 1;
$sql = $db->prepare("SELECT * FROM test WHERE from_id = :id");
$sql->bindValue(':id', $from);

$sql->execute();

while($row = $sql->fetch())
{
    $t =$row['items'];
    $u =$row['to_id'];
    $trans .= "<tr><th>Items</th><th>To</th><th>Status</th></tr><tr><td>$t</td>
            <td>$u</td></tr>";
}

HTML DISPLAY

enter image description here

Improve this question
3
  • The data is not normalised. Get it to 3NF and you'll have a much better and cleaner solution. Commented Oct 30, 2013 at 5:55
  • @Akshay I have no idea how to do that. HOw would that help in getting the data out. Any examples? Commented Oct 30, 2013 at 5:56
  • Here's a great resource - sqa.org.uk/e-learning/SDM04CD/page_01.htm Commented Oct 30, 2013 at 5:59

4 Answers 4

Reset to default
2

Try this!

<?php
    $from = 1;
   $sql = $db->prepare("SELECT * FROM test WHERE from_id = :id");
   $sql->bindValue(':id', $from);

   $sql->execute();

  while($row = $sql->fetch())
    {
     $t =$row['items'];
     $u =$row['to_id'];
             $itemIDs = @explode(",", $t);
         $items = array();
            foreach($itemIDs as $ID){
            $sqlItem = $db->prepare("SELECT itemname FROM itemtable WHERE itemid = :itemid");
            $sqlItem->bindValue(':itemid', $ID);
            $sqlItem->execute();
            $itemname ='';
            while($rowItems = $sqlItem->fetch())
            {
                $itemname .=$rowItems['itemname'];
            }

            $items[$t] = $itemname;

     }   

        $trans .= "<tr><th>Items</th><th>To</th><th>Status</th></tr><tr><td>$items[$t]</td>  <td>$u</td></tr>";
   }

below is my code for testing, 

<?php

  $from = 1;
 $sql = mysqli_query($db,"SELECT * FROM test WHERE from_id = '$from'");  
 while($row = mysqli_fetch_array($sql))
 {

     $t =$row['items'];
     $u =$row['to_id'];
     $itemIDs = @explode(",", $t);
     $itemname ='';
        foreach($itemIDs as $ID){
            $sqlItem = mysqli_query($db, "SELECT itemname FROM itemtable WHERE item_id = '$ID'");           

            while($rowItems = mysqli_fetch_array($sqlItem))
            {
                 $itemname .= $rowItems['itemname'].', ';
            }       


            $items[$u] = $itemname;

     }      
    $trans .= "<tr><th>Items</th><th>To</th><th>Status</th></tr><tr><td>$items[$u]</td>  <td>$u</td></tr>";
  } 

  echo "<table>".$trans."</table>";
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

how come this will return item info from ur code?? it will return only string from code$row['items']code
@Ashish I don't know. It's supposed to return a string
No display. getting the to_id value though just not the item_name
Missed to add the, $sqlItem->execute();. Added that
ok its displaying only one item_name per trans_id. With trans_id of 1 it returns girl upkn which has an item_id of 23
|
2

Note : change my queries with ur need

in ur while loop

while($row = $sql->fetch())
{
$items_array = array();
$items_array = explode(",",$row["items"]);

    foreach($items_array as $key => $value)
    {                                       
        //modify ur query according to ur need
        $query3 = "SELECT  item_name
            FROM item_table 
            WHERE item_id =".$value." ";
        $result3 = mysql_query($query3);
        $row3 = mysql_fetch_assoc($result3);                                            
        $item_name .= $row3['subcategory_name'].", ";

    }
}

now ur array will contains item_id, use foreach loop in ur while loop and get info of Item from item table with item_id from expolode function

Improve this answer

5 Comments

I'll try it with PDO because that is deprecated code you're using
yes ofcourse u change queries..u will get item_id separate in to foreach loop
Don't I need another while loop for query3?
no..if items=21,22,23 then it will execute 3 times with foreachloop..u just try to echo $value in foreach loop u will come to know u got seaparate item_id
Yes, but I need to do it with PDO. mysql_query and mysql_fetch_assoc no longer work
1

Within while you will have to fire new query that will get the information of items. For eg :

"SELECT * FROM item_info_table WHERE id IN (id1,id2, id3)"

It will return you the item information corresponding to the id's.

Improve this answer

2 Comments

yes but my question is how to get the ids out of the db and into that query
Firstly you will have to fetch the ids by executing this query : $sql = $db->prepare("SELECT * FROM test WHERE from_id = :id"); You will retrieve the ids something like this $row['items'] = '21,22,23' Pass this whole data into "SELECT * FROM item_info_table WHERE id IN ($row['items'])" Then it will return you all the data corresponding to these id's
0

The data is not normalized. Get it to normalize and you'll have a much better and cleaner solution.

Improve this answer

1 Comment

Did you just copy/paste this from Ashhay?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.