PHP connecting to two tables in a while loop

Question

I have a small and rather simple question I believe, however I have tried everything but I just can't figure out this syntax.

I will start by entering this code (php):

require_once('connectvars.php'); // My database connection

//Connect to the database
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
//Retrieve the data from the database
$query = "SELECT * FROM pbclickslogin WHERE username='$username'";
$data = mysqli_query($dbc, $query);
while ($info = mysqli_fetch_array( $data )) {
    echo $info['lol']; echo $info['lol2'];
}

So I made up this example and let us say that "$info['lol'];" is a column inside a table named "pbclickslogin" and "$info['lol2'];" is a column inside a table named "pbclickssomethingelse". But I cannot connect to both databases at the same time, or can I?

I have tried adding:

$query = "SELECT * FROM pbclickslogin, pbclickssomethingelse WHERE username='$username'";

and this:

$query = "SELECT * FROM pbclickslogin WHERE username='$username'";
$query2 = "SELECT * FROM pbclickssomethingelse WHERE username='$username'";
$data = mysqli_query($dbc, $query, $query2);
while ($info = mysqli_fetch_array( $data )) {
    echo $info['lol']; echo $info['lol2'];
}

but as some of you experienced PHP users might know. These two things did not solve my problem. Do any of you have an idea of how I can connect to both of these databases while doing the while loop?

When you say "connect to both databases", do you mean connecting to two tables in the same database? Do you need to also modify the DB_HOST or DB_NAME variables between these calls? — John D
– John D, Commented Oct 30, 2012 at 21:36
Yes I meant that :s Sorry I screwed up in databases and tables. I have one database with two tables in it that I need to connect to. The primary key is "username" and then there is different columns that I need to connect to through the two different tables. Thanks for your time! — Peterrr
– Peterrr, Commented Oct 31, 2012 at 9:11

Aamir · Accepted Answer · 2012-10-31 13:20:01Z

1

You can do a join on the two tables instead of two separate queries. Here is a simple example:

    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    $data = mysqli_query($dbc, "SELECT * FROM pbclickslogin, pbclickssomethingelse WHERE pbclickslogin.username = pbclickssomethingelse.username AND pbclickslogin.username='" . $username . "'");
    while ($info = mysqli_fetch_array( $data )) {
        echo $info['lol']; echo $info['lol2'];
    }

edited Oct 31, 2012 at 13:20

answered Oct 30, 2012 at 21:36

Aamir

5,4602 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Peterrr Over a year ago

Awesome, I'll have to try that once I get home from school ;)

Peterrr Over a year ago

Hmm cannot get it to work though it looks quite simple? I get the same error message saying: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/virtual/******.com/public_html/clicks/stats.php It returns a boolean somehow? Maybe because the database/table call went wrong.

Aamir Over a year ago

@Peter: I've updated the query (I changed username='$username' to pbclickslogin.username='$username'). Try now

Peterrr Over a year ago

Oh lol thought I had some serious issues with this code beside my problem, but anyway I still cannot get it to work your way either? Though I have a username column in both tables. Hmmm

Aamir Over a year ago

@Peter: According to the docs, mysqli_query() can sometimes return a boolean depending on the type of query and whether or not the query was successful. I have updated the mysqli_query and changed how the concatenation is being done. Not sure if that will fix it but worth a try..

|

Flosculus · Accepted Answer · 2012-10-30 21:43:28Z

0

SELECT * FROM pbclickslogin l
   INNER JOIN pbclickssomethingelse s ON l.username = p.username
   WHERE l.username='$username'

Best I can do based on the examples you gave. That will join the tables together via the username on both tables, then you select 'where' the username is a specific value.

I seriously recommend you go through some tutorials on PHP and SQL.

answered Oct 30, 2012 at 21:43

Flosculus

6,9563 gold badges22 silver badges44 bronze badges

3 Comments

Peterrr Over a year ago

I am going through tutorials, head first book on PHP and MYSQL but they don't mention this one. Why do you "seriously" recommend it? Are there some serious problems?

Flosculus Over a year ago

well i didnt mean to sound so 'serious' lol, its just the SQL way of connecting tables is to join. you can find tutorials on this, or perhaps joins are covered in later stages of the tutorials you are viewing, in which case, keep reading them

Peterrr Over a year ago

Oh yeah lol I thought maybe I had some really serious issues with my code beside my problem. Thank you for taking your time to response, I know got it working. Have a good day :)

Collectives™ on Stack Overflow

PHP connecting to two tables in a while loop

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related