0

For a schoolproject we have to make a website that interacts with a myphp database. We want to pull data from two different tables in the database, but when we try to echo the data, only data from the first table is shown. Out two tables are: TICKET and VISITOR This is our code:

include 'login.php';

        $sql = mysql_query("SELECT * FROM TICKET VISITOR ",$mysql)
        or die("The query failed!");

        mysql_close($mysql)
        or die("Closing the connection failed!");

        if (!$sql) {
            echo 'Could not run query: ' . mysql_error();
            exit;
        }
        echo "<br/>";   
        while ($row = mysql_fetch_row($sql)) {      
            echo $row[0];
            echo " ";
            echo $row[1];
            echo " ";
            echo $row[2];
            echo "<br/>"; 

        }
Dirk Schotelhoofd
  • 15
  • 7
  • 1
    FYI, [you shouldn't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](https://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which one is best for you. – John Conde Jun 15 '17 at 11:59
  • That's not how you select data from multiple tables – John Conde Jun 15 '17 at 11:59
  • 1
    TICKET and VISITOR are your 2 different tables? – PHP dev Jun 15 '17 at 12:00
  • 1
    you should read joining. https://dev.mysql.com/doc/refman/5.7/en/join.html – urfusion Jun 15 '17 at 12:00
  • `SELECT * FROM TICKET VISITOR ` is not possible...you have use join in select query – PHP dev Jun 15 '17 at 12:03

3 Answers3

1

basically the comments say it already: you can use joins for this task. However I think what you need is a proper introduction into the options and what it all means, so let me try help you there:

First question: What is your problem? If you want to get data from two tables indepedently then what you have two do is to make to separate queries. You more or less run this:

//first run this query
$sql = mysql_query("SELECT * FROM TICKET ",$mysql)
//Run the query and process the data

//then run this query
$sql = mysql_query("SELECT * FROM VISITOR ",$mysql)
//Process this data, too

What you probably actually want to do is to get data from ONE table based on data from ANOTHER table. For example you want to get some data about users and in one table you have their email address and their street address and in the other table you have their name. So you JOIN both tables together based on some information (key) they both contain. So then you go from

TABLE 1: id | email | address
TABLE 2: id | firstname | lastname

To this form:

NEW TABLE: id | email | address | firstname | lastname

There are different kinds of joins. The code for this could look something link this:

$sql = mysql_query("SELECT table1.id, table1.email, table1.address, table2.firstname, table2.lastname FROM TABLE1 LEFT JOIN TABLE2 ON (table1.id = table2.id)",$mysql)

As said before joins are common and properly explained elsewhere. I find this a good tutorial but of course the mentioned documentation (https://dev.mysql.com/doc/refman/5.7/en/join.html) also explained it maybe a bit more condensed.

Gegenwind
  • 1,388
  • 1
  • 17
  • 27
0

Option 1 join

SELECT * FROM TICKET INNER JOIN VISITOR ON NAME;

NAME here is a column name maybe id

Option 2 UNION

SELECT * FROM TICKET UNION SELECT * FROM VISITOR;
Jonathan
  • 2,700
  • 4
  • 23
  • 41
-1

As per your code just insert comma (,) after first table. See below -

$sql = mysql_query("SELECT * FROM TICKET, VISITOR ",$mysql)
        or die("The query failed!");

Also you can use the inner join if any foreign key used on second table.

Use also union.

Aphrodite
  • 1
  • 2