PHP and MySql - fetching rows from two tables

Question

I made product site where I fetch datas about produtcs from database, and I fetch datas about user who posted that product.

In one table are datas for product and in another table are datas for user, in product table is row with id of user who posted that product.

Now, I want to fetch both datas on same page, I don't really know how to do it.

This I made so far:

    <?php
        include 'init.php';

        $id = sanitize($_GET['id']);
        $seller_id = sanitize($_GET['sid']);
        $eur = 7.544967;

        mysql_query(" UPDATE products SET view_count = view_count + 1 WHERE id = '$id' ");  

    $query = mysql_query("SELECT * FROM products, users INNER JOIN product.seller_id = users.id WHERE product.id=".$id);        



while($result = mysql_fetch_assoc($query)){
                    $product_name = $result['product_name'];
                    $img_path = $result['img_path']; 
                    $img_name = $result['img_name'];
                    $condition = $result['condition'];
                    $quantity = $result['quantity'];
                    $country = $result['country'];
                    $price = $result['price'];
                    $pay_method = $result['pay_method'];
                    $shipping = $result['shipping'];
                    $return = $result['return'];
                    $description = $result['description'];                  

                    echo '<div id="sub_container">
                            <div id="image_container">                  
                                <div class="thumb-image">
                                    <img src="'.$img_path.'/'.$img_name.'" data-imagezoom="true" width="500px" height="500px"> 
                            </div>
                            <ul id="img_ul">
                                <li><img src="#" width="80px" height="80px"/></li>
                                <li><img src="#" width="80px" height="80px"/></li>
                                <li><img src="#" width="80px" height="80px"/></li>
                            </ul>                   
                        </div>
                        <div id="product_container">
                            <p><strong>'.$product_name.'</strong></p>
                            <hr>
                            Stanje: '.$condition.' <br><div class="br"></div>
                            Količina: '.$quantity.' <br><div class="br"></div>
                            Zemlja porijekla: '.$country.'<br><div class="br"></div><br>
                            Cijena: '.$price.'kn (~'.round($price/$eur).'€)<div class="br"></div>
                            Način plačanja: '.$pay_method.'<div class="br"></div>
                            Dostava: '.$shipping.'<div class="br"></div>
                            Povrat proizvoda: U roku od '.$return.' dana.<br><div class="br"></div>
                            <div class="br"></div><br><div class="br"></div>                                    
                        </div>
                        <ul id="aside_container">
                            <li>
                                <div id="aside">                    
                                    <img src="'.$seller_img_path.'/'.$seller_img_name.'" width="50px" height="50px"/>
                                    <a href="#">'.$seller_username.'</a>                    
                                    <br>
                                    '.$seller_points.'
                                    <hr>
                                    Broj pregleda: '.$result['view_count'].'<br>
                                    <a href="#">Dodaj na karticu</a><br>
                                    <a href="#">Dodaj u listu želja</a><br>
                                </div>
                            </li>
                            <li>
                                <div id="aside_buy">
                                    Boja: <select>
                                            <option value="red">Crvena</option>
                                            <option value="blue">Plava</option>
                                        </select><br><div class="br"></div>
                                    Veličina: <select>
                                        <option value="X">X</option>
                                        <option value="XL">XL</option>
                                    </select><br><div class="br"></div>
                                    Količina: <input type="number" name="quantity" id="quantity" value="1"/><br><br><br>
                                    <a id="buy_button" href="#">Kupi proizvod</a>
                                </div>
                            </li>
                        </ul>           
                    </div>
                    <div id="description_container">
                        <p><strong>Opis proizvoda</strong></p>
                        <hr>                        
                        <div id="description">'.$description.'</div>
                    </div>';
    ?>

$user_query is query for fetching datas from user table and vars in my code with seller word are fetched from that table, I tried with while loop but then my site is slow.

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Sep 14 '15 at 17:15
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Sep 14 '15 at 17:15
simple put join where each table col value is equal to other table col value, rough example `select * from products, user where product table.id=user.id and product.id='$id'` — Shehary, Sep 14 '15 at 17:16
@JayBlanchard I made `sanitize()` function for preventing it — CroVG, Sep 14 '15 at 17:18
@CroVG Any SQL tutorial should explain how to write a `JOIN`. — Barmar, Sep 14 '15 at 17:22
@CroVG A `sanitize` function is always a problem, most people get it completely wrong, and I'd bet yours is completely inadequate. Use prepared statements because otherwise you'd be using `mysql_real_escape_string` like you're supposed to. — tadman, Sep 14 '15 at 17:23
@Shehary not sure about join, can you check? I have error now `$query = mysql_query("SELECT * FROM products, users INNER JOIN product.seller_id = users.id WHERE product.id=".$id); ` — CroVG, Sep 14 '15 at 17:28
i believe it's just typo mistake here `product.seller_id = users.id` look closer `product.seller_id` on otherhand table name is `products` — Shehary, Sep 14 '15 at 17:34
@Shehary now no errors, but I am not got whai I need, nowit's fetch me just datas what are related, but I want all datas from bouth tables — CroVG, Sep 14 '15 at 17:36
@CroVG this gives you an idea of inner join and the mistakes your are making http://www.w3schools.com/sql/sql_join_inner.asp — Shehary, Sep 14 '15 at 17:37

score 0 · Accepted Answer · answered Sep 15 '15 at 14:06

0

I just had to join my tables then run $query.

Like this:

$query = mysql_query("SELECT * FROM products, users WHERE products.seller_id=users.id AND products.id='$id'");

Thanks everybody for help.

answered Sep 15 '15 at 14:06

CroVG

149
2
13

PHP and MySql - fetching rows from two tables

1 Answers1