CODEWITHSUNDEEP
phpmysqlfetch
jediah
jediah

Reputation: 102

mysql fetch by data from other table

I have built a simple webshop, where i need to process ordered products. The order comes in very nice with the id of the ordered product.

I want the name and price of the product displayed on the page where i show all details about of the order. Too bad it doesn't work..

This is my attempt:

code to fetch product(s) ordered:

$query2 = mysql_query("SELECT * FROM orders_products WHERE order_id='".$_GET['order']."'");
while ($line2 = mysql_fetch_assoc($query2))
{
$row2[] = $line2;
$smarty->assign('row2', $row2);
}

(attempt to) fetch product name and price:

$query4 = mysql_query("SELECT * FROM products WHERE id ='".$row2['product_id']."'");
while ($line4 = mysql_fetch_assoc($query4))
{
$row4[] = $line4;
$smarty->assign('row4', $row4);
}

displaying it (excuse me for some dutch words in the html):

                <div class="module">
                <h2><span>Bestelde Producten</span></h2>
                {section name=row2 loop=$row2}
                 {section name=row4 loop=$row4}
                <div class="module-table-body">
                    <table id="bestelling" class="bestelling">
                        <thead>
                            <tr>
                                <th style="width:3%">#</th>
                                <th style="width:10%">Naam</th>
                                <th   style="width:10%">Bedrag</th>                         
                                <th style="width:3%">Aantal</th>                                    
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>{$row2[row2].id}</td>
                                <td>{$row4[row4].name}</td>
                                <td>{$row4[row4].price}</td>
                                <td>{$row2[row2].product_amount}</td>

                            </tr>
                        </tbody>
                    </table></br>
                    <p><strong>Totaal: </strong></br>
                    <strong>BTW (21%): </strong></p>
                    </br>
                     <p><strong>Totaal Inclusief BTW: </strong></p>
                     {/section}
                    <div style="clear: both"></div>
                 </div> <!-- End .module-table-body -->         
                 </div>     
                 </div>

orders_products table:

id order_id product_id product_amount

products table:
id category_id name description slug price in_stock

Thanks guys for your answers! Kami's code worked out for me.

How i have it now: 

$queryx = mysql_query("SELECT * FROM products inner join orders_products on products.id = orders_products.product_id WHERE order_id = '".mysql_real_escape_string($_GET['order'])."'");

while ($linex = mysql_fetch_assoc($queryx))
{
$rowx[] = $linex;
$smarty->assign('rowx', $rowx);
}

Do you think this is safe? I will start using mysqli or PDO eventually, but i find it too dificult as i am still a beginner..

Do you know any good guides about securing php -> mysql?

Thanks so far!

Upvotes: 1

Views: 290

Answers (2)

Barmar
Barmar

Reputation: 781004

Use a join:

select o.id, o.product_amount, p.name, p.price
from orders_products o join products p
on p.id = o.product_id
where o.id = '" . mysql_real_escape_string($_GET['order']) . "'"

If you're just starting out, you should NOT use the mysql_XXX functions, they are deprecated and easy to get SQL injection (you need to use mysql_real_escape_string religiously to avoid it). You should use mysqli or PDO with prepared statements to avoid this problem.

Upvotes: 1

Kami
Kami

Reputation: 19407

try

$queryx = mysql_query("SELECT * FROM products inner join orders_products on products.id = orders_products.product_id WHERE order_id = " . $_GET['order']);

while ($linex = mysql_fetch_assoc($queryx))
{
    $rowx[] = $linex;
    $smarty->assign('rowx', $rowx);
}

I am doing join to link the data together through one query. This will allow you to have all the product info in one query. Your issue I suspect are the quotes around order_id='".$_GET['order']."'".

Upvotes: 1

Related Questions