Create PHP to execute MySQL query from PHPMyAdmin 1

[Geronantimo]

I have a query that I run in phpMyAdmin.

After running the query, I can click to "Create PHP Code".

How can I use this code to create a webpage with PHP to output the results of the query to the browser?

The PHP code created by phpMyAdmin after running the query is this:

$sql = 'select puu.email, pm.sendstart, pb.status,'
        . ' case'
        . ' when instr(data, "Delivery to the following recipients failed.") > 0 then "Delivery failed."'
        . ' when instr(data, "Unknown user") > 0 then "Unknown user"'
        . ' when instr(data, "User unknown") > 0 then "Unknown user"'
        . ' when instr(data, "malformed address") > 0 then "malformed address"'
        . ' when instr(data, "No such user") > 0 then "No such user"'
        . ' when instr(data, "mailbox unavailable") > 0 then "mailbox unavailable"'
        . ' when instr(data, "retry timeout exceeded") > 0 then " retry timeout exceeded"'
        . ' when instr(data, "Mailbox unknown or not accepting mail.") > 0 then "Mailbox unknown or not accepting mail."'
        . ' else "General Failure ..."'
        . ' end as reason,'
        . ' substring(data,1,1000)'
        . ' from ww_user_message_bounce as pumb'
        . ' join ww_user_user as puu on pumb.user = puu.id'
        . ' join ww_bounce as pb on pb.id = pumb.bounce'
        . ' join ww_message as pm on pm.id = pumb.message';

I have added added the database connection information to the beginning of the PHP script:

<?php

  $user = "username";
  $pass = "password";
  $host = "localhost";
  $db = "database";

  mysql_connect($host, $user, $pass);
  mysql_select_db($db);

I am using phpMyAdmin 2.9.0.2, with PHP version 4.4.3 and MySQL 4.1.21

Any help would be appreciated.

 

[vacunita]

You'll have to execute the query, using mysql_query(), and then get the resulkts back and echo them as an example:

$results=mysql_query($sql);

while($row=mysql_fetch_array($results)){
echo $row['fieldname1'] . "," . $row['fieldname2'] . "," ... "<br>";
}

This would output each row of data , with the fields separated by commas, and the rows separated by a line break.

you can if you want to include html code to present the data in a table. echo out the table tags prior to the while loop, and then echo the rows with the appropriate row and cell tags

echo "<tr><td>" . $row['fieldname1']. "</td><td>....</tr>";

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Geronantimo]

Hi vacunita,

Many thanks for your reply.

I know very little about php, but I followed your suggestions and after a while I achieved my objective.

$result = mysql_query('select SUBSTRING_INDEX(email, \'@\', -1) as domain, count(*) as bounces'
        . ' from ww_user_user PUU'
        . ' join ww_user_message_bounce PUMB on PUU.id = PUMB.user'
        . ' group by domain order by bounces desc ');

while($row=mysql_fetch_array($result)){
//echo $row['domain'] . "," . $row['bounces'] . "<br>";
echo "<table><tr><td>" . $row['domain']. "</td><td>" . $row['bounces']. "</td></tr></table>";

It took me a while to realise what "fieldname1" and "fieldname2" were supposed to be!

 

[vacunita]

O.k maybe i should have called them columnname1 and 2.

Glad its working for you now.

As i said you should output the table tags before the while loop, and just output the row and cell tags inside the loop.

[red]echo "<table>";[/red]
while($row=mysql_fetch_array($result)){
//echo $row['domain'] . "," . $row['bounces'] . "<br>";
echo "<tr><td>" . $row['domain']. "</td><td>" . $row['bounces']. "</td></tr>";
}
[red]echo "</table>";[/red]


That way you don't create a different table for each row of data, but rather make them rows of the same table. Unless of  course you want a table for each row. 

In any case glad its working for you.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Geronantimo]

As i said you should output the table tags before the while loop, and just output the row and cell tags inside the loop.

Yes, you're absolutely right!

Thanks again.