CODEWITHSUNDEEP
phpmysqlselect
Genesis
Genesis

Reputation: 1347

MySQL + PHP - Select info based on previous select result

I'm doing an integration of PHP and MySQL. The following code is working fine:

<?php
include_once("conf.php");

$sql = "SELECT Name,Address FROM customers";
$rs = mysqli_query($connect,$sql) or die("can't connect to DB");
$temp = mysqli_num_rows($rs);


while($row = mysqli_fetch_array($rs))
{
  $convert = mb_convert_encoding ($row['Name'],"UTF-8","gbk");
  $print .= "<tr><td>".$convert."</td><td>".$row['Address']."</td><td>".$sql2."</td></tr>\n";
}

This code is working, but now I need to select specific information from ANOTHER table, BASED on the result (Name) of this one.

So, this would be the other MySQL select:

$sql2 = "select History from Delivery where Name="$convert";

Based on the "Name" of my customer (from customer table), I need to get his "History" at the Delivery table.

So, I will print his: Name + Address + History

What would be the best solution??

@@@@@@@@@ SOLVED @@@@@@@@@@

$sql = 'SELECT * FROM customers c 
            INNER JOIN Delivery d 
            ON c.Name = d.Name';

$result = mysqli_query($connect, $sql);
$temp = mysqli_num_rows($rs);


while($row = mysqli_fetch_array( $result))
{
  $convert = mb_convert_encoding ($row['Name'],"UTF-8","gbk");
  $print .= "<tr><td>".$convert."</td><td>".$row['Address']."</td><td>".$row['History']."</td></tr>\n";
}

My current situation is: I have three tables from which I need to get info.

Tables: customers, users, history.

From customers I need ammount of customers.

From Delivery I need History (based on ammount of customers).

From users I need Name (actually it's almost a nickname).

Since that these three tables have a 'Name' column and I CAN'T change column names, how can I get the Name from USERS instead of CUSTOMERS or DELIVERY using that INNER JOIN statement??

Upvotes: 0

Views: 390

Answers (3)

dweiss
dweiss

Reputation: 832

Darkeden, you can do SELECT customers.* or you could do SELECT customers.name as 'CName' and etc. Using aliases like this, you can later do echo $row['CName'].

Upvotes: 1

nickb
nickb

Reputation: 59699

Do a join on the first table to grab all the data you need at one time:

$sql = 'SELECT * FROM customers c 
            INNER JOIN Delivery d 
            ON c.Name = d.Name';

$result = mysqli_query($connect, $sql);

while($row = mysqli_fetch_array( $result))
{
    echo $row['History'] . ' ' . $row['Name'];
}

This saves you from needing the second query.

Upvotes: 1

dweiss
dweiss

Reputation: 832

It sounds like you just want to do an INNER JOIN with these two tables on name?

Upvotes: 1

Related Questions