loop through tables

slevytam

Hi,

Could someone tell me how to loop through every table in a database and then run an update query.

Thanks for the help,

DeadFly

What exactly are you trying to do . . . ? ? ? (put it into some context for us)

Update every field in every table of a database ?

slevytam

Thanks for your reply,

I would like to run this query for ever table in a database without knowing the table names.

loop through each table in database {
mysql_query("Delete from table where condition=true");
}

Thanks,

DeadFly

If you knew the table names I could help you for sure, but I've never come across a request like yours, so I can't help.
Sorry.
🙁

slevytam

Thanks for your time,

I guess I will just have to use a huge array with all the table names. (Was hoping to save some considerable time)

--SL

DeadFly

Well the way I would do it, if you could get the names listed into an array that'd be great.

Pseudo-code example:

///// Put the tables into an array somehow.
$DBArray=array(0=>'TableA', 1=>'TableB', 2=>'TableC');

///// Start the loop
for ($n=0;$n<count($DBArray);$n++) {
    $query ='DELETE FROM '.$DBArray[$n].' ';
    $query.='WHERE fieldname=criteria';
    if (!mysql_query($query)) {
        ///// do error message or whatever if it doesn't work.
        echo 'Table data in '.$DBArray[$n].' NOT deleted.';
    }else{
        ///// do nothing or some kind of status message.
        echo 'Table data in '.$DBArray[$n].' deleted.';
    }
}

That's how I would approach your problem.
Or is that too simplified ?

slevytam

Looks great. I was hoping to do this without table names to save time and work in the future when the database expands...

I guess the easy way isn't always possible...

--SL

DeadFly

Try this:

<?
///// db connection info
if (!mysql_connect('host', 'user', 'password')) {
    print 'Could not connect to mysql';
    exit;
}

$dbname = 'database';
$result = mysql_list_tables($dbname); 

///// if result isn't going to work then write error message
if (!$result) {
    print "DB Error, could not list tables\n";
    print 'MySQL Error: ' . mysql_error();
    exit;
}

///// loop & put all table names into an array
while ($row = mysql_fetch_row($result)) {	
	$DBArray[]=$row[0];	
}

///// loop and do specific query to each table in the array
for ($n=0;$n<count($DBArray);$n++) { 

///// make the query
$query ='DELETE FROM '.$DBArray[$n].' '; 
$query.='WHERE fieldname=criteria';

///// run the query 
if (!mysql_query($query)) { 
    ///// do error message or whatever if it doesn't work. 
    echo 'Table data in '.$DBArray[$n].' NOT deleted.'; 
}else{ 
    ///// do nothing or some kind of status message. 
    echo 'Table data in '.$DBArray[$n].' deleted.'; 
} 
}
?>

In the query, the fieldname may have to be the same in each table for it to work correctly.

slevytam

The field name is the same. This should work...

Thank you very much!