I'm stuck on 'merging' tables

Question

Iwas just trying to merge tables, by pairing rows, for example: cw_users.userid = sold.buyer_id

I want to show which packages they bought (vps, domain, hosting).

So here's my code. Hope you can help

Edit: I forgot to mention I also need to use $_SESSION['id'], which will be linked with cw_users.userid

<?php
            //connect.php
            $server     = 'localhost';
            $username   = 'myusernamenotyours';
            $password   = 'mypassnotyours';
            $database   = 'mydatabasenotyours';

            if(!mysql_connect($server, $username, $password))
            {
                exit('Error: could not establish database connection');
            }
            if(!mysql_select_db($database))
            {
                exit('Error: could not select the database');
            }

            $sql = "SELECT sold.*, vps_packages.*
                    FROM sold, vps_packages
                    WHERE sold.bought_vps = vps_packages.id ";

                $result = mysql_query($sql);

                if(!$result)
                {
                    echo 'De packages kunnen niet worden geladen';
                }
                else
                {
                    if(mysql_num_rows($result) == 0)
                    {
                        echo 'Er zijn nog geen packages, probeer het later nog eens.';
                    }
                    else
                    {
                        //prepare the table
                        echo '<table>';

                        while($row = mysql_fetch_assoc($result))
                        { 
                            echo '<li>' . sold.bought_vps .  ' </li>';
                        }
                    }
                }
            echo '</table>';
        ?>

Read up on JOINS http://www.sitepoint.com/understanding-sql-joins-mysql-database/ — Funk Forty Niner, Oct 07 '14 at 20:08
Please, [don't use `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 statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Oct 07 '14 at 20:10
Mixing `li`s in `table`s can and will lead to problems.. and you should access your `rows` values like this: `$row['bought_vps']` — Félix Adriyel Gagnon-Grenier, Oct 07 '14 at 20:17

Mateusz B · Answer 1 · 2014-10-07T20:20:48.300

0

while($row = mysql_fetch_assoc($result))
{ 
 echo '<li>' . sold.bought_vps .  ' </li>';
}

You should treat $row as curent iterated line of joined table so if You put print_r($row), You will see all data in line.

Other SQL:

SELECT * FROM sold
INNER JOIN vps_packages ON sol.bougth_vps = vps_packages.id

edited Oct 07 '14 at 20:20

answered Oct 07 '14 at 20:14

Mateusz B

19
3

Your SQL has an extra comma and a typo. It's also just a rewrite of his existing SQL. Am I missing something? I don't see what this answer adds? – AndySavage Oct 07 '14 at 20:19
1

I don't think `sold.bought_vps` is going to work at all in PHP. – tadman Oct 07 '14 at 20:21
It displays the same: "soldbought_vps", which is not exactly what i was looking for. Thanks though. – Jeroen Oct 07 '14 at 20:22
It was only example, that You should use join instead on Where and comparing ids. – Mateusz B Oct 07 '14 at 20:22
In this case they are doing the same thing (test it with an EXPLAIN). The join just makes it clearer what is joining logic and what is filtering logic (for most people). His SQL was still perfectly valid. – AndySavage Oct 07 '14 at 20:26

score 0 · Accepted Answer · answered Oct 07 '14 at 20:25

your while should be like this:

while($row = mysql_fetch_assoc($result))
{ 
  echo '<tr><td>' . $row['bought_vps'] .  ' </td></tr>';
}

also you should echo '<table>' where it will be echoed even if your query fails, otherwise your page won't display correctly.

but before going/coding anything further, STOP, listen and think

you are using old deprecated dangerous mysql_* extension. see the comment on your question as to alternatives
You are trying to call a value defined as bought_vps in MySQL with the same name in PHP. It does not make any sense. variables called without $ are globals, and I'm pretty sure you are not trying to echo a global at that place in the code.

You should know this

what this means is that you should really, really consider following simple tutorials to learn the very bases before anything else. If this really was a problem for you, you should acquire more skills before going any further.

I only worked with easier SQL queries so far. It didn't make any sense to me to use $row since there were 2 tables selected. Anyway, it works. Thanks a lot. — Jeroen, Oct 07 '14 at 20:35

I'm stuck on 'merging' tables

2 Answers2