CODEWITHSUNDEEP
phpmysqlmysqli
Semger
Semger

Reputation: 263

Selecting rows from multiple tables

I would like to know what the fastest way is to make the following SQL call using PHP. I am using procedural mySQLi.

$dcount = "SELECT max(id) FROM deposits";
$result = mysqli_query($conn,$dcount);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
    $count = $row["max(id)"];
    echo $count;
    echo ",";

}
} else {
echo '0';
}

//second query
$ucount = "SELECT max(int) FROM anothertable";
$result2 = mysqli_query($conn,$ucount);
if (mysqli_num_rows($result2) > 0) {
while($row = mysqli_fetch_assoc($result)) {
    $count = $row["max(int)"];
    echo $count;
    echo ",";

}
} else {
echo '0';
}

Is there a way to make the execution faster than like this, maybe to echo the results from both queries after the first if statement?

It just seems rather long to me.

Thanks in advance.

Upvotes: 0

Views: 41

Answers (4)

Mike
Mike

Reputation: 1231

$dcount = "
SELECT max(id) as max,'deposit' as table_name FROM deposits
UNION
SELECT max(id) as max,'another' as table_name FROM anothertable
"; 

$result = mysqli_query($conn,$dcount);
if (mysqli_num_rows($result) > 0){
  while($row = mysqli_fetch_assoc($result)){
    echo $row["table_name"].":".$row["max"]."\n";
  }
} else {
  echo '0';
}

Upvotes: 1

Double H
Double H

Reputation: 4160

SELECT max(id) as max_id, max(int) as max_int FROM deposits ,anothertable

Upvotes: 1

Marvin Fischer
Marvin Fischer

Reputation: 2572

SELECT d.max(id) as d_max_id, a.max(int) as a_max_int FROM deposits as d JOIN anothertable as a ON d.id = a.id;

is what you need for multiple tables

$row['d_max_id']

will give you deposits.max(id) now

You have to edit the d.id = a.id accordingly to what you want the two tables to match on

If you cant join try this:

SELECT max(id) as max_id, (SELECT max(int) FROM anothertable) as max_int FROM deposits;

Upvotes: 1

Raphioly-San
Raphioly-San

Reputation: 404

SELECT max(id) as max_id, (SELECT max(int) as max_int FROM anothertable) as max_int
FROM deposits

Not tested, but something like it should work

Upvotes: 2

Related Questions