Improve PHP Array to use on AJAX

Question

I created a PHP file to populate a page, using AJAX, but I can't find a solution to my problem.

Here's my PHP and its Outputs:

$result = mysql_query("SELECT id, product, picture FROM table1 ORDER BY id DESC");

$products = array();
while($product = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $products[] = ($product);
}

$json = json_encode($products);
$output = isset($_GET['callback']) ? "{$_GET['callback']}($json)" : $json;
echo $output;

This will print:

[{"id":"5","product":"product5","picture":"picture5.jpg"},
{"id":"4","product":"product4","picture":"picture4.jpg"},
{"id":"3","product":"product3","picture":"picture3.jpg"},
{"id":"2","product":"product2","picture":"picture2.jpg"},
{"id":"1","product":"product1","picture":"picture1.jpg"}]

I want to add the field "In_Stock" on this Output using a another query to output something like this:

[{"id":"5","product":"product5","picture":"picture5.jpg","in_stock":"yes"},
{"id":"4","product":"product4","picture":"picture4.jpg","in_stock":"no"},
{"id":"3","product":"product3","picture":"picture3.jpg","in_stock":"yes"},
{"id":"2","product":"product2","picture":"picture2.jpg","in_stock":"yes"},
{"id":"1","product":"product1","picture":"picture1.jpg","in_stock":"no"}]

My question is: Its possible to use the value of the array (Inside the first While) to do a search in another table, add this value to products array and keep the same "layout" on the output above?

EDIT:

These are my tables:

TABLE1

id
product
picture

And the second one

TABLE2

id  
user
product_id
in_stock

The same product may have different stocks depending on the User...

What you ask is possible, but why not retrieve all the data in the SQL query? — user1864610
– user1864610, Commented Jul 24, 2014 at 20:51
Yes, you can run a single query inside your while loop to another table, and manually add the array key from the new query. A join on the original query would be more efficient though. — scrowler
– scrowler, Commented Jul 24, 2014 at 20:51
If the stock is in another table you can use a JOIN clause: dev.mysql.com/doc/refman/5.0/en/join.html — Gordnfreeman
– Gordnfreeman, Commented Jul 24, 2014 at 20:51

dave · Accepted Answer · 2014-07-24 21:26:13Z

2

Yes, you could do another query, but it probably makes more sense to just alter the first query to include all the data you need, which would look something like this:

 mysql_query("SELECT table1.id, table1.product, table1.picture, table2.in_stock 
              FROM table1 
              LEFT JOIN table2 ON (table1.id = table2.product_id 
              AND table2.user = " . intval($_SESSION['user']) . ") 
              ORDER BY table1.id DESC");

Edit: Added in the user from session per your comment. I used intval because I am assuming you are using an integer for the id of the user, and I don't know how the $_SESSION value got set - if it is from user input then it should be escaped.

As a side note, mysql_query is deprecated, you should look into mysqli and prepared statements.

edited Jul 24, 2014 at 21:26

answered Jul 24, 2014 at 20:51

dave

65.1k6 gold badges81 silver badges98 bronze badges

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

4 Comments

suicidebilly Over a year ago

Sorry, i forgot to say that this second query will use dynamic values from Sessions, so the second table have different values for the same product, according the logged user. ex: User: user1, Product: product.id, In_Stock: yes.

dave Over a year ago

Show the table structure, you can just as easily use values from session in the first query as you could in the second.

suicidebilly Over a year ago

Thanks, now it worked, i just need to hide the NULL output that appears if the searched produt does not have references on Table2. I tried to use - AND table2.in_stock = 'yes')- But the outputs returns Null on products without any reference on Table2.

dave Over a year ago

In that case, change LEFT JOIN to INNER JOIN and then if it is null in table2 it won't show up in the results at all

Collectives™ on Stack Overflow

Improve PHP Array to use on AJAX

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related