PHP execute multiple queries and while loop

Question

I am having some difficulty executing multiple queries.

I have been reading about mysqli_multi_query however I'm not sure how to implement this into my existing code.

Ideally I need to query two separate tables and display the results within the same while loop. Should this be a do-while, or am I way off?

My current code is;

$id = $_GET['id'];

//setup first query
$query = ("SELECT pub_id, title FROM vw_ft_search WHERE pub_id = $id");

//setup second query
$query = ("SELECT phys_desc FROM vw_physical_descriptions WHERE publication_id = $id");

$result = $conn->query($query);

if($result === false) {
    trigger_error('Wrong SQL: ' . $query . ' Error: ' . $conn->error, E_USER_ERROR);
}

    while($row = $result->fetch_assoc()){

        //result from first query
        if (!empty($row['pub_id'])){ echo $row['pub_id'] . '<br />'; }
            else echo "no information" .'<br />';

        //result from first query
        if (!empty($row['title'])){ echo $row['title'] . '<br />'; }
            else echo "no information" .'<br />';

        //result from second query here

    }

New to this so any help/advice is appreciated.

WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you will create severe SQL injection bugs. NEVER put $_GET data directly into a query. — tadman
– tadman, Commented Jul 8, 2015 at 16:21
Thanks for the advice @tadman this is really useful. The problem that I seem to be having as a novice is that there are so many (different and out of date) tutorials out there it's hard to find a good one to follow. — jonboy
– jonboy, Commented Jul 9, 2015 at 8:04
PHP the Right Way is a great place to start. I'll also mention that if you're serious about developing applications you'll want to find a development framework like Laravel that suits your style and needs, then learn that inside and out. The core PHP library is extremely primitive and requires a lot of grunt work to get anything non-trivial done with it, whereas a good framework will do most of the heavy lifting for you. — tadman
– tadman, Commented Jul 9, 2015 at 15:40

Machavity · Accepted Answer · 2015-07-08 16:20:17Z

1

Assuming these are 1:1 records you should try to JOIN your queries instead. Also, I fixed your SQL injection

$id = $conn->real_escape_string($_GET['id']);
$query = "SELECT vfs.pub_id, vfs.title, vpd.phys_desc
    FROM vw_ft_search vfs
        INNER JOIN vw_physical_descriptions vpd ON vfs.pub_id = vpd.publication_id
    WHERE vfs.pub_id = $id";

answered Jul 8, 2015 at 16:20

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

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

3 Comments

tadman Over a year ago

It's much better to fix with a placeholder. This might have a vulnerability if someone does something dumb between the first line and the second. Still, a step up, go good to catch that!

jonboy Over a year ago

thanks @Machavity this is working. Can I just ask, why ave you prefixed them vfs. and vpd. - can they be prefixed anything?

Machavity Over a year ago

I aliased the table names and fields. It's a good coding practice because you never guess which table you're referencing. If you choose not to alias the tables you can just write the full table name in front

Matthew B · Accepted Answer · 2015-07-08 16:21:06Z

0

The best way is to use a join statement:

SELECT pub_id, title, phys_desc FROM vw_ft_search 
JOIN vw_physical_descriptions ON vw_physical_descriptions.publication_id=vw_ft_search.pub_id
WHERE vw_ft_search.pub_id = $id"

The JOIN mushes the table together, the ON tells the server how to match the data up.

answered Jul 8, 2015 at 16:21

Matthew B

848 bronze badges

Comments

Jon · Accepted Answer · 2015-07-09 04:16:51Z

0

Can $result = $conn->query($query); handle multi query? Maybe you should be using

$result = $conn->multi_query($query);

answered Jul 9, 2015 at 4:16

Jon

297 bronze badges

Collectives™ on Stack Overflow

PHP execute multiple queries and while loop

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related