How to build an associative array with 2 sqls?

Question

I want to build an associative array, which will run two mysql tables. Name of tables: cs_lots , cs_lots_history .

My PHP CODE -

$rows = array();
$res2 = mysql_query("SELECT * FROM cs_lots WHERE active_lot='1'") or die(mysql_error());
$result1 = mysql_query("SELECT DISTINCT id_lot FROM cs_lots_history WHERE user_steamid='".$_SESSION['steamid']."'") or die(mysql_error());
while($row = mysql_fetch_array($res2)) {
    $rows []= array(
        'id' => $row['id'],
        'inv_id' => $row['inv_id'],
        'inv_assets' => $row['inv_assets'],
        'name' => $row['inv_name'],
        'inv_image' => $row['inv_image'],
        'inv_rarity' => $row['inv_rarity'],
        'inv_color' => $row['inv_color'],
        'inv_type' => $row['inv_type'],
        'inv_price' => $row['inv_price'],
        'price_ticket' => $row['price_ticket'],
        'maxUsers' => $row['places'],
        'nowUsers' => $row['now_places'],
        'my' => false
      );
}

Array with this code:

LOTS = [{"id":"166","inv_id":"989","inv_assets":"3432669422","name":"Redline ","inv_image":"image","inv_rarity":"Field-Tested","inv_color":"d32ce6","inv_type":"1","inv_price":"2105.97","price_ticket":"14","maxUsers":"240","nowUsers":"1","my":false},
{"id":"167","inv_id":"929","inv_assets":"3551634073","name":"Hyper Beast ","inv_image":"image","inv_rarity":"Battle-Scarred","inv_color":"eb4b4b","inv_type":"1","inv_price":"924.43","price_ticket":"8","maxUsers":"180","nowUsers":"0","my":false},
{"id":"168","inv_id":"1104","inv_assets":"3313740799","name":"Asiimov ","inv_image":"image","inv_rarity":"Battle-Scarred","inv_color":"eb4b4b","inv_type":"1","inv_price":"1495.00","price_ticket":"13","maxUsers":"180","nowUsers":"19","my":false},
{"id":"169","inv_id":"847","inv_assets":"3603670527","name":"Jaguar ","inv_image":"image","inv_rarity":"Battle-Scarred","inv_color":"eb4b4b","inv_type":"1","inv_price":"2711.65","price_ticket":"13","maxUsers":"320","nowUsers":"8","my":false},
{"id":"170","inv_id":"1100","inv_assets":"3313741398","name":"Asiimov ","inv_image":"image","inv_rarity":"Field-Tested","inv_color":"eb4b4b","inv_type":"1","inv_price":"2756.70","price_ticket":"16","maxUsers":"260","nowUsers":"10","my":false},
{"id":"171","inv_id":"899","inv_assets":"3551642235","name":"Atomic Alloy ","inv_image":"image","inv_rarity":"Factory New","inv_color":"d32ce6","inv_type":"1","inv_price":"862.50","price_ticket":"8","maxUsers":"180","nowUsers":"1","my":false}];

Now my array running with one mysql table and i have only my:false But i need change key my and value false to true where SQL $result1 have same result $result1['id_lot'] with $row['id'] . As Example: If $result1['id_lot'] = $row['id'] i need this code:

{"id":"166","inv_id":"989","inv_assets":"3432669422","name":"Redline ","inv_image":"image","inv_rarity":"Field-Tested","inv_color":"d32ce6","inv_type":"1","inv_price":"2105.97","price_ticket":"14","maxUsers":"240","nowUsers":"1","my":true}`

Thanks.

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Oct 01 '15 at 20:18
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Oct 01 '15 at 20:18
@JulioSoares tables related by ID-ID_LOT. http://i.shotnes.com/a/01/p4ppwpia.uqt_560d974a798da.png (cs_lots_history) and http://i.shotnes.com/a/01/qiew22ux.4ml_560d972019096.png (cs_lots) — private person, Oct 01 '15 at 20:26
Instead of 2 queries, do 1 query with a `LEFT JOIN` -> `SELECT .... FROM cs_lots_history LEFT JOIN cs_lots ON cs_lots_history.id_lot = cs_lots.id ...`. Then if there is a matching row in `cs_lots` you can do a ternary, ie.`'my' => !empty($result1['id']) ? true : false;`, where `$result1['id']` is the `cs_lots.id` — Sean, Oct 01 '15 at 20:31
Then that's as @Sean said... since there is a relationship you can have a single query with a join to retrive the data at once in the very way you want it. — Julio Soares, Oct 01 '15 at 20:40
@Sean Your version does not work :( http://pastebin.com/yTrwiyDW - new code. I have 12 items in table cs_lots . And i have 9 items in cs_lots_history, where user has it... With this query displays only those items that have the same table cs_lots_history, but there is not all the things ... And there different users bought them. — private person, Oct 01 '15 at 21:00
My query example was `FROM cs_lots_history LEFT JOIN cs_lots`, but you have the reverse `FROM cs_lots LEFT JOIN cs_lots_history`. So which table do you want to return all of, and which one do you want to return only matching rows? My query returns all of `cs_lots_history` and appends matching `cs_lots`. Where your query returns all of `cs_lots` and appends matching `cs_lots_history`. — Sean, Oct 01 '15 at 21:36
@Sean after 1 hour i do it... ;DD thanks you very much for notices. — private person, Oct 01 '15 at 22:15

score 1 · Answer 1 · answered Oct 01 '15 at 22:16

LEFT JOIN + aliases:

$rows = array();
$res2 = mysql_query("SELECT cs_lots_history.id, cs_lots_history.user_steamid, cs_lots_history.id_lot, cs_lots.id as csid, inv_id, inv_assets, inv_image, inv_color, inv_name, inv_rarity, inv_type, inv_price, price_ticket, places, now_places FROM cs_lots LEFT JOIN cs_lots_history ON cs_lots.id = cs_lots_history.id_lot WHERE active_lot='1' OR user_steamid='".$_SESSION['steamid']."' GROUP BY cs_lots.id") or die(mysql_error());

$a=0;
while($row = mysql_fetch_array($res2)) {
    $rows []= array(
        'id' => $row['csid'],
        'inv_id' => $row['inv_id'],
        'inv_assets' => $row['inv_assets'],
        'name' => $row['inv_name'],
        'inv_image' => $row['inv_image'],
        'inv_rarity' => $row['inv_rarity'],
        'inv_color' => $row['inv_color'],
        'inv_type' => $row['inv_type'],
        'inv_price' => $row['inv_price'],
        'price_ticket' => $row['price_ticket'],
        'maxUsers' => $row['places'],
        'nowUsers' => $row['now_places'],
        'my' => !empty($row['id_lot']) ? true : false   
    );
}

Thanks @Sean.

How to build an associative array with 2 sqls?

1 Answers1