geatzo Posted May 5 Share Posted May 5 I have a trade table where users can add pokemon for trade. I then have a offer table where other users can make offers on this pokemon from the trade table. So i have a trade table then another trade for offer. In the offer table the user can offer muti of there pokemon for the pokemon in the trade table. So i can offer 2 - 3 of my pokemon for there 1 pokemon. I have made a column called oid for each trade. I then print out the offers for the pokemon from the trade table which works but i would like to group them. So all offers from oid would show in 1 box then the next box would be the next oid and so on. Instead it just shows all . So at the moment there are 3 offers in the table offer 4,5,6 (oid) so id like it to show 3 boxes with each offer(oid) with each result for that offer (oid) inside the table. I really hope this makes sense. <?php $password = mysqli_real_escape_string($link,$_GET['id']); $offerid = strip_tags($password); $limit = 50; $s = $db->prepare("SELECT * FROM trade_offers WHERE offer_on = ? "); $s->execute(array($offerid)); $allResp = $s->fetchAll(PDO::FETCH_ASSOC); // echo '<pre>'; // var_dump($allResp); $total_results = $s->rowCount(); $total_pages = ceil($total_results/$limit); echo $total_results ; if (!isset($_GET['page'])) { $page = 1; } else{ $page = $_GET['page']; } $start = ($page-1)*$limit; $stmt = $db->prepare("SELECT * FROM trade_offers WHERE offer_on = ? ORDER BY oid DESC LIMIT $start, $limit"); $stmt->execute(array($offerid)); // set the resulting array to associative $stmt->setFetchMode(PDO::FETCH_OBJ); $results = $stmt->fetchAll(); // var_dump($results); $no = $page > 1 ? $start+1 : 1; ?> <div id="scroll" style="overflow-x: visible;"> <div id="content"> <div id="loading" style="height: 1133px; width: 800px; visibility: hidden;"><p style="text-align: center; margin-top: 150px;"><b>Loading</b><br><img src="./Pokémon Vortex - Trade Station_files/pokeball_loading.gif" width="50" height="50"></p></div> <div id="ajax"> <h2 class="heading-maroon no-bot-border-rad margin-bottom-3">Trading Station</h2> <?php require_once 'sections/tradenav.php'; ?> <?php foreach($results as $result){ $stmt = $db->prepare("SELECT * FROM user_pokemon WHERE id=?"); $stmt->execute([$result->pokemon_id]); $user = $stmt->fetch(); $stmt2 = $db->prepare("SELECT * FROM pokemon WHERE name=?"); $stmt2->execute([$user['name']]); $user2 = $stmt2->fetch(); ?> <table class="table-striped width-100 text-center" cellpadding="10"> <tbody> <tr><th></th> <th class="text-left width-200px">Pokémon</th> <th>Level</th> <th>Experience</th> <th>Moves</th> <th>Offer By:</th> <th>Offered On</th> </tr> <tr> <td class="pokeball-small"> <img src="https://ascensionrpg.net/img/pokemon/<?= $user['type']; ?>/<?= $user2['id']; ?>.png"> </td> <td class="text-left"> <b> <a href="" onclick="pokedexTab('pid=48575929', 1); return false;"> <?= $user['name']; ?> <i class="ion-male male"></i> </a> </b> </td> <td> <?= $user['level']; ?> </td> <td> <?= $user['exp']; ?> </td> <td> <?= $user['move1']; ?><br> <?= $user['move2']; ?><br> <?= $user['move3']; ?><br> <?= $user['move4']; ?> </td> <td><?= $result->offer_from ; ?></td> <td> <p> <?php $stmt22 = $db->prepare("SELECT * FROM trade WHERE id=?"); $stmt22->execute([$offerid]); $user22 = $stmt22->fetch(); $stmt222 = $db->prepare("SELECT * FROM pokemon WHERE name=?"); $stmt222->execute([$user22['pokemon_name']]); $user222 = $stmt222->fetch(); ?> <a class="tooltip" href="" onclick="pokedexTab('pid=4627030', 1); return false;"> <img src="https://ascensionrpg.net/img/pokemon/<?= $user22['pokemon_type']; ?>/<?= $user222['id']; ?>.png"> <span class="text-center"><?= $user22['pokemon_name']; ?> </span> </a> </p> <form method="post" onsubmit="get('/trade/remove-offer/4627030/1714855885/',''); disableSubmitButton(this); return false;"> <input type="submit" class="button-small button-maroon" value="Remove"> </form> <form method="post" onsubmit="get('/trade/remove-offer/4627030/1714855885/',''); disableSubmitButton(this); return false;"> <input type="submit" class="button-small button-maroon" value="Accept"> </form> </td></tr> <?php } ?> </tbody></table> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 5 Share Posted May 5 the simplest way of doing this, without adding variables, conditional logic, and repeated code, to start a new section and close out the previous section, only when appropriate, is to index/pivot the data using oid value when you fetch it. this will result in an array of data that looks like this - Array ( [4] => Array ( [0] => Array ( [0] => row of data for oid 4 ) ) [5] => Array ( [0] => Array ( [0] => row of data for oid 5 ) ) [6] => Array ( [0] => Array ( [0] => row of data for oid 6 ) [1] => Array ( [0] => row of data for oid 6 ) [2] => Array ( [0] => row of data for oid 6 ) ) ) you can then loop over this data to produce the output using code like this - foreach($data as $oid=>$arr) { // start a new section here echo "start a section for oid: $oid<br>"; // loop over the row(s) of data for this section foreach($arr as $row) { // output each row of data in the section echo '---a row of data<br>'; } // end the section echo 'end this section<br>'; } which produces this output - start a section for oid: 4 ---a row of data end this section start a section for oid: 5 ---a row of data end this section start a section for oid: 6 ---a row of data ---a row of data ---a row of data end this section Quote Link to comment Share on other sites More sharing options...
geatzo Posted May 5 Author Share Posted May 5 9 hours ago, mac_gyver said: $oid=>$arr i dont understand this part ? I think you have worked out what id like todo which is great. So id do a select to grab all the oid then use the foreach i just dont understand where im getting there $ from? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 5 Share Posted May 5 the data layout and the nested foreach loops assume you have done this - 9 hours ago, mac_gyver said: index/pivot the data using oid value when you fetch it code to do that looks like this - // index/pivot the data using the oid value as the main array index (PDO has a fetch mode to do this, but for clarity, this example uses explicit logic) $data = []; while($row = $stmt->fatch()) { $data[$row['oid']][] = $row; } Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 5 Share Posted May 5 while you are pondering that, here's a number of points for the posted code - don't use any _escape_string() function when you are using a prepared query. just use a prepared query. don't mix mysqli functions with the PDO extension. if you are converting old code to use the PDO extension, name the PDO connection variable $pdo so that anyone looking at the code or searching in it can determine which extension is being used at any point. the $_GET['id'] input is required. if it's not valid, setup and display an error message instead of running the code that's dependent on that input. don't use strip_tags, ever. don't select all your columns and matching rows of data to get a count of rows for pagination. use a SELECT COUNT(*) ... query instead. if there is no data matching the offer id input, display a message stating so, instead of running the rest of the code trying to produce output from the data. if you set the default fetch mode to assoc when you make the database connection, you won't need to specify it in any of the fetch statements. don't switch/mix fetch modes in your code. rowCount() is not guaranteed to work with a SELECT query. after you change to using a SELECT COUNT(*) ... query, you just fetch the count value from that query. don't use SELECT * in queries. list out the columns you are selecting. as posted in your previous thread, you need to use a single JOIN query to get related data all at once. the posted code can accomplish its work with three queries - 1) to get the count of matching rows for pagination, 2) to get the page of main data, and 3) to get the trade 'for' pokemon data (which you should only do once, not repeatedly inside of a loop.) the javascript onclick and onsubmit code has some hard-coded pid values in it. you will need to get these values from the query to use in the code. when using the short-print tags, you can leave out white-space and the ;, e.g. <?=$row['type']?> is all you need to echo a variable. you need to validate the resulting web pages at validator.w3.org Quote Link to comment Share on other sites More sharing options...
geatzo Posted May 5 Author Share Posted May 5 1 hour ago, mac_gyver said: while you are pondering that, here's a number of points for the posted code - don't use any _escape_string() function when you are using a prepared query. just use a prepared query. don't mix mysqli functions with the PDO extension. if you are converting old code to use the PDO extension, name the PDO connection variable $pdo so that anyone looking at the code or searching in it can determine which extension is being used at any point. the $_GET['id'] input is required. if it's not valid, setup and display an error message instead of running the code that's dependent on that input. don't use strip_tags, ever. don't select all your columns and matching rows of data to get a count of rows for pagination. use a SELECT COUNT(*) ... query instead. if there is no data matching the offer id input, display a message stating so, instead of running the rest of the code trying to produce output from the data. if you set the default fetch mode to assoc when you make the database connection, you won't need to specify it in any of the fetch statements. don't switch/mix fetch modes in your code. rowCount() is not guaranteed to work with a SELECT query. after you change to using a SELECT COUNT(*) ... query, you just fetch the count value from that query. don't use SELECT * in queries. list out the columns you are selecting. as posted in your previous thread, you need to use a single JOIN query to get related data all at once. the posted code can accomplish its work with three queries - 1) to get the count of matching rows for pagination, 2) to get the page of main data, and 3) to get the trade 'for' pokemon data (which you should only do once, not repeatedly inside of a loop.) the javascript onclick and onsubmit code has some hard-coded pid values in it. you will need to get these values from the query to use in the code. when using the short-print tags, you can leave out white-space and the ;, e.g. <?=$row['type']?> is all you need to echo a variable. you need to validate the resulting web pages at validator.w3.org Im still trying to work out how id go about it using my code. My weak point has always been the foreach function. In the muti websites i have ive avoided it at all cost Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 5 Share Posted May 5 here's example code i came up with for you to examine and learn from. it's tested logically, but obviously not with any query data - <?php // initialization $limit = 50; // pagination rows per page $errors = []; // array to hold user/validation errors // post method form processing - none in this example // get method business logic - get/produce data needed to display the page // inputs: // $_GET['id'] offer id - required // $_GET['page'] pagination requested page - optional, default to 1 // condition the offer id input $offerid = intval(trim($_GET['id']??0)); // validate the input if(!$offerid) { // if you see this error it is either due to a programming mistake or someone submitting their own data or no data, not your data $errors['offerid'] = 'Offer id is required'; } else { // note: if only a logged in user, with ownership or administrator-ship over the offer id values can view this page, you must have a user login system, and you would need to verify ownership of the submitted offer id value // get a count of the total number of offers matching the offer id input $sql = "SELECT COUNT(*) FROM trade_offers WHERE offer_on = ?"; $stmt = $pdo->prepare($sql); $stmt->execute([$offerid]); if(!$total_results = $stmt->fetchColumn()) { // no matching data // since the offer id link/form should have been built from actual offer data, // if you see this error it is either due to a programming mistake or someone submitting their own data, not your data $errors['offerid'] = 'There is no offer data to display'; } else { // there is offer data // produce pagination values $total_pages = ceil($total_results/$limit); // condition the page input $page = intval(trim($_GET['id']??1)); $start = ($page-1)*$limit; // get a page of offer data matching the offer id input // columns used in current output - t1.offer_from, t2.name, t2.type, t2.level, t2.exp, t2.move1-4, t3.id // t1.oid is used to index the data $sql = "SELECT t1.oid, t1.offer_from, t2.name, t2.type, t2.level, t2.exp, t2.move1, t2.move2, t2.move3, t2.move4, t3.id FROM trade_offers t1 JOIN user_pokemon t2 ON t1.pokemon_id = t2.id JOIN pokemon t3 ON t2.name = t3.name WHERE t1.offer_on = ? ORDER BY t1.oid DESC LIMIT ?,?"; // supply start and limit via prepared query place-holders $stmt = $pdo->prepare($sql); $stmt->execute([ $offerid,$start,$limit ]); // index/pivot the data using the oid as the main array index (PDO has a fetch mode to do this, but for clarity, this example uses explicit logic) $data = []; while($row = $stmt->fatch()) { $data[$row['oid']][] = $row; } // get the trade 'for' data corresponding to the offer id input // the name/type data in the trade table should be an id pointing to actual mame/type, not the actual name and type values $sql = "SELECT t1.pokemon_name, t1.pokemon_type, t2.id FROM trade t1 JOIN pokemon t2 ON t1.pokemon_name = t2.name WHERE t1.id=?"; $stmt = $pdo->prepare($sql); $stmt->execute([$offerid]); $trade_for = $stmt->fetch(); } } // html document ?> <div id="scroll" style="overflow-x: visible;"> <div id="content"> <div id="loading" style="height: 1133px; width: 800px; visibility: hidden;"><p style="text-align: center; margin-top: 150px;"><b>Loading</b><br><img src="./Pokémon Vortex - Trade Station_files/pokeball_loading.gif" width="50" height="50"></p></div> <div id="ajax"><h2 class="heading-maroon no-bot-border-rad margin-bottom-3">Trading Station</h2> <?php require_once 'sections/tradenav.php'; ?> <?php // display any errors if(!empty($errors)) { echo '<p>'.implode('<br>',$errors).'</p>'; } ?> <?php // display offer data if(!empty($data)) { // loop over the page of offer data matching the offer id input foreach($data as $oid=>$arr) { // start a new section here // start the table, start the thead, output the tr, end the thead, and start the tbody ?> <table class="table-striped width-100 text-center" cellpadding="10"> <thead> <tr><th></th> <th class="text-left width-200px">Pokémon</th> <th>Level</th> <th>Experience</th> <th>Moves</th> <th>Offer By:</th> <th>Offered On</th> </tr> </thead> <tbody> <?php // loop over the row(s) of data for this section foreach($arr as $row) { // output each row of data in the section // output each tr ?> <tr> <td class="pokeball-small"> <img src="https://ascensionrpg.net/img/pokemon/<?=$row['type']?>/<?=$row['id']?>.png"> </td> <td class="text-left"> <b> <a href="" onclick="pokedexTab('pid=48575929', 1); return false;"> <?=$row['name']?> <i class="ion-male male"></i></a> </b> </td> <td><?=$row['level']?></td> <td><?=$row['exp']?></td> <td><?=$row['move1']?><br> <?=$row['move2']?><br> <?=$row['move3']?><br> <?=$row['move4']?></td> <td><?=$row['offer_from']?></td> <td> <p> <a class="tooltip" href="" onclick="pokedexTab('pid=4627030', 1); return false;"> <img src="https://ascensionrpg.net/img/pokemon/<?=$trade_for['pokemon_type']?>/<?=$trade_for['id']?>.png"> <span class="text-center"><?=$trade_for['pokemon_name']?></span> </a> </p> <form method="post" onsubmit="get('/trade/remove-offer/4627030/1714855885/',''); disableSubmitButton(this); return false;"> <input type="submit" class="button-small button-maroon" value="Remove"> </form> <form method="post" onsubmit="get('/trade/remove-offer/4627030/1714855885/',''); disableSubmitButton(this); return false;"> <input type="submit" class="button-small button-maroon" value="Accept"> </form> </td> </tr> <?php } // end the section // end the tbody and end the table ?> </tbody> </table> <?php } } Quote Link to comment Share on other sites More sharing options...
geatzo Posted May 5 Author Share Posted May 5 (edited) 47 minutes ago, mac_gyver said: here's example code i came up with for you to examine and learn from. it's tested logically, but obviously not with any query data - <?php // initialization $limit = 50; // pagination rows per page $errors = []; // array to hold user/validation errors // post method form processing - none in this example // get method business logic - get/produce data needed to display the page // inputs: // $_GET['id'] offer id - required // $_GET['page'] pagination requested page - optional, default to 1 // condition the offer id input $offerid = intval(trim($_GET['id']??0)); // validate the input if(!$offerid) { // if you see this error it is either due to a programming mistake or someone submitting their own data or no data, not your data $errors['offerid'] = 'Offer id is required'; } else { // note: if only a logged in user, with ownership or administrator-ship over the offer id values can view this page, you must have a user login system, and you would need to verify ownership of the submitted offer id value // get a count of the total number of offers matching the offer id input $sql = "SELECT COUNT(*) FROM trade_offers WHERE offer_on = ?"; $stmt = $pdo->prepare($sql); $stmt->execute([$offerid]); if(!$total_results = $stmt->fetchColumn()) { // no matching data // since the offer id link/form should have been built from actual offer data, // if you see this error it is either due to a programming mistake or someone submitting their own data, not your data $errors['offerid'] = 'There is no offer data to display'; } else { // there is offer data // produce pagination values $total_pages = ceil($total_results/$limit); // condition the page input $page = intval(trim($_GET['id']??1)); $start = ($page-1)*$limit; // get a page of offer data matching the offer id input // columns used in current output - t1.offer_from, t2.name, t2.type, t2.level, t2.exp, t2.move1-4, t3.id // t1.oid is used to index the data $sql = "SELECT t1.oid, t1.offer_from, t2.name, t2.type, t2.level, t2.exp, t2.move1, t2.move2, t2.move3, t2.move4, t3.id FROM trade_offers t1 JOIN user_pokemon t2 ON t1.pokemon_id = t2.id JOIN pokemon t3 ON t2.name = t3.name WHERE t1.offer_on = ? ORDER BY t1.oid DESC LIMIT ?,?"; // supply start and limit via prepared query place-holders $stmt = $pdo->prepare($sql); $stmt->execute([ $offerid,$start,$limit ]); // index/pivot the data using the oid as the main array index (PDO has a fetch mode to do this, but for clarity, this example uses explicit logic) $data = []; while($row = $stmt->fatch()) { $data[$row['oid']][] = $row; } // get the trade 'for' data corresponding to the offer id input // the name/type data in the trade table should be an id pointing to actual mame/type, not the actual name and type values $sql = "SELECT t1.pokemon_name, t1.pokemon_type, t2.id FROM trade t1 JOIN pokemon t2 ON t1.pokemon_name = t2.name WHERE t1.id=?"; $stmt = $pdo->prepare($sql); $stmt->execute([$offerid]); $trade_for = $stmt->fetch(); } } // html document ?> <div id="scroll" style="overflow-x: visible;"> <div id="content"> <div id="loading" style="height: 1133px; width: 800px; visibility: hidden;"><p style="text-align: center; margin-top: 150px;"><b>Loading</b><br><img src="./Pokémon Vortex - Trade Station_files/pokeball_loading.gif" width="50" height="50"></p></div> <div id="ajax"><h2 class="heading-maroon no-bot-border-rad margin-bottom-3">Trading Station</h2> <?php require_once 'sections/tradenav.php'; ?> <?php // display any errors if(!empty($errors)) { echo '<p>'.implode('<br>',$errors).'</p>'; } ?> <?php // display offer data if(!empty($data)) { // loop over the page of offer data matching the offer id input foreach($data as $oid=>$arr) { // start a new section here // start the table, start the thead, output the tr, end the thead, and start the tbody ?> <table class="table-striped width-100 text-center" cellpadding="10"> <thead> <tr><th></th> <th class="text-left width-200px">Pokémon</th> <th>Level</th> <th>Experience</th> <th>Moves</th> <th>Offer By:</th> <th>Offered On</th> </tr> </thead> <tbody> <?php // loop over the row(s) of data for this section foreach($arr as $row) { // output each row of data in the section // output each tr ?> <tr> <td class="pokeball-small"> <img src="https://ascensionrpg.net/img/pokemon/<?=$row['type']?>/<?=$row['id']?>.png"> </td> <td class="text-left"> <b> <a href="" onclick="pokedexTab('pid=48575929', 1); return false;"> <?=$row['name']?> <i class="ion-male male"></i></a> </b> </td> <td><?=$row['level']?></td> <td><?=$row['exp']?></td> <td><?=$row['move1']?><br> <?=$row['move2']?><br> <?=$row['move3']?><br> <?=$row['move4']?></td> <td><?=$row['offer_from']?></td> <td> <p> <a class="tooltip" href="" onclick="pokedexTab('pid=4627030', 1); return false;"> <img src="https://ascensionrpg.net/img/pokemon/<?=$trade_for['pokemon_type']?>/<?=$trade_for['id']?>.png"> <span class="text-center"><?=$trade_for['pokemon_name']?></span> </a> </p> <form method="post" onsubmit="get('/trade/remove-offer/4627030/1714855885/',''); disableSubmitButton(this); return false;"> <input type="submit" class="button-small button-maroon" value="Remove"> </form> <form method="post" onsubmit="get('/trade/remove-offer/4627030/1714855885/',''); disableSubmitButton(this); return false;"> <input type="submit" class="button-small button-maroon" value="Accept"> </form> </td> </tr> <?php } // end the section // end the tbody and end the table ?> </tbody> </table> <?php } } <b>Fatal error</b>: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''400','50'' at line 9 in /var/www/vhosts/geatzo.com/ascensionrpg.net/offered.php:143 Stack trace: #0 /var/www/vhosts/geatzo.com/ascensionrpg.net/offered.php(143): PDOStatement->execute() #1 {main} thrown in <b>/var/www/vhosts/geatzo.com/ascensionrpg.net/offered.php</b> on line <b>143</b><br /> seems has tho its not getting the id i noticed you used $offerid = intval(trim($_GET['id']??0)); im guessing you didnt mean to put the ??0 so i fixed that but still the same error on $stmt->execute([$offerid,$start,$limit ]); ive been looking though this $sql = "SELECT t1.oid, t1.offer_from, t2.name, t2.type, t2.level, t2.exp, t2.move1, t2.move2, t2.move3, t2.move4, t3.id FROM trade_offers t1 JOIN user_pokemon t2 ON t1.pokemon_id = t2.id JOIN pokemon t3 ON t2.name = t3.name WHERE t1.offer_on = ? ORDER BY t1.oid DESC LIMIT ?,?"; t2.name, t2.type, t2.level, t2.exp, t2.move1, t2.move2, t2.move3, t2.move4, t3.id isnt in trade_offers Edited May 5 by geatzo Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 5 Share Posted May 5 (edited) the sql query error is because the LIMIT x,y values must be numeric. they are strings in your case because you are using emulated prepared queries and are suppling an array of values to the ->execute([...]) call. you need to use true prepared queries, which properly carries the datatype through to the database server when suppling the values to the ->execute([...]) call. when you make the database connection, you need to - set the character set to match your database tables (so that no character conversion occurs over the connection and in the case of emulated prepared queries, so that php can properly escape string data so that any sql special characters in a value cannot break the sql query syntax.) set the error mode to exceptions (this is the default setting now in php8+, but it doesn't hurt to set it.) set the emulated prepared query setting to false (you want to run true/real prepared queries.) set the default fetch mode to assoc (so that you don't need to specify the fetch mode in each fetch statement.) Edited May 5 by mac_gyver Quote Link to comment Share on other sites More sharing options...
geatzo Posted May 5 Author Share Posted May 5 ive edited my post $sql = "SELECT t1.oid, t1.offer_from, t2.name, t2.type, t2.level, t2.exp, t2.move1, t2.move2, t2.move3, t2.move4, t3.id FROM trade_offers t1 JOIN user_pokemon t2 ON t1.pokemon_id = t2.id JOIN pokemon t3 ON t2.name = t3.name WHERE t1.offer_on = ? ORDER BY t1.oid DESC LIMIT ?,?"; it looks like your trying to grab the level exp and moves from the offer table when these are stored in the trade table Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 5 Share Posted May 5 58 minutes ago, geatzo said: it looks like your trying to grab the level exp and moves from the offer table when these are stored in the trade table nope. the t2 alias name is for the - JOIN user_pokemon t2 table, because that's what the code you posted is getting the original $user data elements from. we only see the information you supply and the answers cannot be any better than the posted information. the only query for the trade table in your original posted code is for the trade 'for' data. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.