Jump to content
_fr99rf_

Two queries into one

Recommended Posts

Hi everyone, I'm having troubles with query from database. I want to select all from one table and select sum from second table in the same time. Is this possible?

Here is my script:

$conn = mysqli_connect('localhost', 'root', '', 'test');

if (!mysqli_set_charset($conn, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($conn));
}

$sql = "SELECT first_id, first_name FROM first";
$sql = "SELECT sum(total) as SumTotal FROM second";

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

while ($data = mysqli_fetch_array($result)) {
    echo '<tr>';
    echo '<th>'.$data['first_id'].'</th>';
    echo '<th>'.$data['first_name'].'</th>';
    echo '<th>'.$data['SumTotal'].'</th>';
    echo '</tr>';
}

mysqli_close($conn);

Thanks!


Share this post


Link to post
Share on other sites

Is there a common key in both table to relate the records or do you want the same total to appear on every output row

Share this post


Link to post
Share on other sites

Hi Barand, thanks for your quick reply. If I did understand correctly, there is foreign key with relation between.

Maybe my question is not set up correctly..

 

I want make query with "SELECT" to 2 different tables?

 

Thanks!

Share this post


Link to post
Share on other sites

No I made that display sum total but from all city, I want to display from city that are in one common state.

 

For example:

 

You have table of cities that call table1, and you have table2 with group of another cities.

I wan to display total number of citizens form table1 and table2, but separated, not total sum from both.

 

I hope I did explain correctly...

$conn = mysqli_connect('localhost', 'root', '', 'test');

if (!mysqli_set_charset($conn, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($conn));
}

$sql = "SELECT * FROM table1, table2";
$result = mysqli_query($conn, $sql);

function numCitizens($conn) {
    $data = $conn->query("SELECT sum(citizens) as numCitizensFROM table2")->fetch_array();
    return $data["numCitizens"];
}
$sum = numCitizens($conn);

while ($data = mysqli_fetch_array($result)) {
    echo '<tr>';
    echo '<th>'.$data['table1_id'].'</th>';
    echo '<th>'.$data['table1_name'].'</th>';
    echo '<th>'.$sum.'</th>';
    echo '</tr>';
}

mysqli_close($conn);
Edited by _fr99rf_

Share this post


Link to post
Share on other sites

If I'm reading your question correctly, you're going to want to look into the GROUP BY clause. Something along the lines of

SELECT	 a.city_name
	,SUM(b.citizens) AS numCitizens
FROM table1 a
LEFT JOIN table2 b
	ON a.cityID = b.cityID
GROUP BY numCitizens

No guarantee this will work if you copy and paste it (I'm only on my second up of coffee this morning), but it should get you moving in the right direction. Also, note that this assumes a foreign key of cityID in both table1 and table2 which you use to make the join.

Share this post


Link to post
Share on other sites

If I'm reading your question correctly, you're going to want to look into the GROUP BY clause. Something along the lines of

SELECT	 a.city_name
	,SUM(b.citizens) AS numCitizens
FROM table1 a
LEFT JOIN table2 b
	ON a.cityID = b.cityID
GROUP BY numCitizens

No guarantee this will work if you copy and paste it (I'm only on my second up of coffee this morning), but it should get you moving in the right direction. Also, note that this assumes a foreign key of cityID in both table1 and table2 which you use to make the join.

 

Well, thank you Max, I will give a try and let you know If I am on the right way... Thanks, and enjoy your second coffee :happy-04:  

Share this post


Link to post
Share on other sites

You need to GROUP BY city_name with that query

 

Thank you - you're absolutely right! :sleeping:

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.