[RESOLVED] Select Query Problem

[obrienkev]

Hi all,

I want to select all the data from one field in various database tables. Each field in each table has the same name.

At the moment the query displays nothing?

$query = "SELECT m.name, r.name, s.name, sp.name ".
                  "FROM mergers as m, rights as r, spin_offs as s, splits as sp";

[Roger_Ramjet]

I'm not surprised it's not returning the correct data, but I am surprised that it is not returning anything?? Are you sure you do not have othet errors in your code?

That said - the way to do this is with a UNION query

$sql = "SELECT name FROM mergers 
            UNION SELECT name FROM rights
            UNION SELECT name FROM spin_offs
            UNION SELECT name FROM splits";

[Lars_Berg]

A union seems to be more appropriate.

SELECT name
from mergers union all
select name
from rights union all
select name
from spin_offs union all
select name 
FROM splits

Your query will return all possible combinations of records from the tables, e.g. if each table contains 100 rows the result would contain 100 milion rows.

[obrienkev]

Thanks.

I now want give the user the option to delete entries from the database tables.

Below is my code. The user selects each checkbox to delete a file and hits submit.

So considering the files are from different tables how do I use the delete query to operate on different tables?

Thanks again.

echo '<input type="checkbox" name="file['$row['id']']" id="file[]" value="'.$row['name'].'" />'.$row['name'].'<br />';

[Roger_Ramjet]

Again: ARE YOU SURE YOU DO NOT HAVE OTHER ERRORS IN YOUR CODE?

Try some basic debugging, echo num_rows to see if anything is being returned: cos it's obviously not, any more than your original query did.
Run the same query in the mysql console or phpmyadmin or whatever to see if you get any results, then examine your php code for the error that is obviously there.
Finally post the WHOLE code AS YOU ARE USING IT - not some edited version to confuse us all - so that someone here can help you.

PS don't use Lars's UNION ALL - you don't want everything joined to everything, just a simple list with no duplicates.

[obrienkev]

That part is working fine. Thanks for your help.

Now I want to be able to delete any of the tables field data. How do I do this??

Here's my code to select data from tables:

$query = "SELECT name FROM mergers ".
         "UNION SELECT name FROM rights ".
         "UNION SELECT name FROM spin_offs ".
         "UNION SELECT name FROM splits";
//echo $query.'<br />';

$result = mysql_query($query) or die(mysql_error());
/*$num_results = mysql_num_rows($result);
echo $num_results;*/

echo '<form action="file_del.php" name="frmbrowse" method="post"><p>';
while ($row = mysql_fetch_array($result))
{	
 			echo '<input type="checkbox" name="file[]" id="file[]" value="'.$row['name'].'" />'.$row['name'].'<br />';
}
echo '<br /><input type="submit" name="submit" value="Submit" /><br /><br /></p></form>';

[Roger_Ramjet]

You need to add an extra field to the union query to identify which table each 'name' comes from.

// either

$query = "SELECT 1 AS tbl, name FROM mergers ".
         "UNION SELECT 2 AS tbl, name FROM rights ".
         "UNION SELECT 3 AS tbl, name FROM spin_offs ".
         "UNION SELECT 4 AS tbl, name FROM splits";

// and use a switch statement on the first column (the integer) to identify table of origin

// or just use a string for the first column that contains the table name itself

$query = "SELECT 'mergers' AS tbl, name FROM mergers ".
         "UNION SELECT 'rights' AS tbl, name FROM rights ".
         "UNION SELECT 'spin_offs' AS tbl, name FROM spin_offs ".
         "UNION SELECT 'splits' AS tbl, name FROM splits"; 

// you could then simply concatenate the tbl value into your delete query

The important thing here is the version of mysql you are using. Before 4.1, the string values would all be set to the same length as the first, ie 'mergers'=7 chars, which would truncate any names over 7 chars long. Putting the longest name first would overcome this of course.

[obrienkev]

The user selects what tables to delete from:

echo '<form action="file_del.php" name="frmbrowse" method="post"><p>';
while ($row = mysql_fetch_array($result))
{	
 			echo '<input type="checkbox" name="file[]" id="file[]" value="'.$row['name'].'" />'.$row['name'].'<br />';
}
echo '<br /><input type="submit" name="submit" value="Submit" /><br /><br /></p></form>';

So then in the subsequent page the code should delete from the selected tables...
How do I incorporate the above code into this?

foreach ($file as $key => $value)
{
....
}

thanks.

[Roger_Ramjet]

You need to include the table along with the 'name'. Personally, I'd make it into a comma-seperated string so that you can process it easily with explode().

while ($row = mysql_fetch_array($result))
{    

            echo '<input type="checkbox" name="file[]" id="file[]" value="' . $row['tbl'] . ',' . $row['name'].'" />'.$row['name'].'<br />';
} 

// then

foreach ($file as $val) {
   $data = explode(',' , $val);
   $tbl = $data[0];
   $name = $data[1];
   $sql = "DELETE FROM $tbl WHERE name='$name' ";
 }

[obrienkev]

OK sorry if I'm not understanding the code.

Here is my first page where the user selects what tables to delete the data from:

// connect to db
include ('../../db_connect.php');

$query = "SELECT name FROM mergers ".
         "UNION SELECT name FROM rights ".
         "UNION SELECT name FROM spin_offs ".
         "UNION SELECT name FROM splits";
//echo $query.'<br />';

$result = mysql_query($query) or die(mysql_error());
/*$num_results = mysql_num_rows($result);
echo $num_results;*/

echo '<form action="file_del.php" name="frmbrowse" method="post"><p>';
while ($row = mysql_fetch_array($result))
{	
 			echo '<input type="checkbox" name="file[]" id="file[]" value="'.$row['name'].'" />'.$row['name'].'<br />';
}
echo '<br /><input type="submit" name="submit" value="Submit" /><br /><br /></p></form>';
echo '<hr />';

Then the code should delete the selected tables:

// declare variables
$file = $_POST['file'];

// connect to database
include('../../db_connect.php');

foreach ($file as $key => $value)
{
 				echo $key. '=>' .$value. '<br /><br />';

			// Perform Query
			$query = "DELETE FROM excel WHERE name = '$value'";
			//echo $query.'<br /><br />';
			$result = mysql_query($query) or die(mysql_error());

			unlink($current_dir.'/'.$value);
			if (unlink)
			{
			 echo '<p>Deleted</p>';
			}
}

Where does the $row['tbl'] value come from??

<input type="checkbox" name="file[]" id="file[]" value="' . $row['tbl'] . ',' . $row['name'].'" />

Thanks for all your help & patience.

[Roger_Ramjet]

It comes from the UNION query which I posted earlier. By including it in the union query you can identify which table the 'name' came from. No use the user choosing to pick a 'name' to delete from table 'rights' if infact it comes from table 'spin_offs'.

Now you are outlining a different problem to that originally posed. If you want the user to choose the table to delete from then you had better display the names by table, not as an aggregate from all tables. Or are the SAME names in every table?. If they are then the union query is redundant.

[obrienkev]

The same field names are in each table. The user has the choice to delete any file from each table.

I want to display all the files in each database table. Then the user checks each one to delete.

How would I do this??

Sorry if it wasn't clear earlier.

[Roger_Ramjet]

You are no clearer now 😕

obrienkev wrote:

The same field names are in each table. The user has the choice to delete any file from each table.

'field names' ?? Sure you don't mean COLUMN names. 😃
I'm sure you do.

obrienkev wrote:

I want to display all the files in each database table. Then the user checks each one to delete.

How would I do this??

To get the contents/data from each table, you use the UNION query with the additional 'tbl' column so that each row in the result can be linked back to the table it is in

$query = "SELECT 'mergers' AS tbl, name FROM mergers ".
         "UNION SELECT 'rights' AS tbl, name FROM rights ".
         "UNION SELECT 'spin_offs' AS tbl, name FROM spin_offs ".
         "UNION SELECT 'splits' AS tbl, name FROM splits";  

Now you will have a set of results that looks like this
tbl, name
'mergers', 'file1'
'mergers', 'file2'
'rights', 'file3'
'rights', 'file4'
etc

Then when the user selects 'file2' or 'file3' you know which tables to delete each from.

Now, my guess is that you want to process multiple selections, and that will mean multiple queries. Yes, mysql can handle multi-table deletes (in some versions), but this is not a case for that. Multiple table deletes are for deleting related records from several tables at the same time, and involve the use of JOINS. That is not what we have here so using such a query would be complex overkill.

Just query each table seperately and display the data for each table in a seperate html table and/or form. Then process each submit seperately for each table.

[obrienkev]

My problem now is that if two tables have the same name and the user only wants to delete a file of that name from just one table. How is this done?

e.g. tableA has fileNew
tableB has fileOld
tableC has fileNew

I am unable to delete fileNew from tableA without deleting fileNew from tableC.

My code...

foreach ($file as $key => $value)
{
 				echo $key. '=>' .$value. '<br /><br />';

			// Perform Query
			$query = "DELETE FROM mergers WHERE name = '$value'";
			echo $query.'<br /><br />';
			$result = mysql_query($query) or die(mysql_error());

			// Perform Query
			$query = "DELETE ".
						 	 "FROM rights ".
							 "WHERE ".
							 "name = '$value'";
			echo $query.'<br /><br />';
			$result = mysql_query($query) or die(mysql_error());

			// Perform Query
			$query = "DELETE ".
						 	 "FROM spin_offs ".
							 "WHERE ".
							 "name = '$value'";
			echo $query.'<br /><br />';
			$result = mysql_query($query) or die(mysql_error());

			// Perform Query
			$query = "DELETE ".
						 	 "FROM splits ".
							 "WHERE ".
							 "name = '$value'";
			echo $query.'<br /><br />';
			$result = mysql_query($query) or die(mysql_error());
}

[Roger_Ramjet]

I've already told you how to do this, try reading my replies in future.

You use the table name that has been concatenated to the filename to build the delete query. Just use this code even if you do not understand it, cos I'm not going to go over this again.

// get the contents of the $_POST array which contains the TABLE name and the data 
foreach ($_POST['file'] as $val) {
   // convert the string into an array so that you can use the 2 parts: the table name and the file name
   $data = explode(',' , $val);
   // the table name is the 1st element of the new array
   $tbl = $data[0];
   // the file name is the 2nd element of the new array
   $name = $data[1];
   // now build the delete query using the table name and the data
   $sql = "DELETE FROM $tbl WHERE name='$name' ";
   // now run the query you built
   mysql_query($sql;
 } 

It's as simple as that. If you don't understand it then I give up

[obrienkev]

Thanks for that and I really appreciate your help.

Just dont understand where $tbl is getting its value from.

Here's my checkbox code:

echo '<input type="checkbox" name="file[]" id="file[]" value="'.$row['name'].'" />'.$row['name'].'<br />';

How can I put tbl into that as a value from the query?? That's my sticky point. Don't see how you did it in the code above.

tbl is just used in the first query:

$query = "SELECT 'mergers' AS tbl, name FROM mergers ".
         "UNION SELECT 'rights' AS tbl, name FROM rights ".
         "UNION SELECT 'spin_offs' AS tbl, name FROM spin_offs ".
         "UNION SELECT 'splits' AS tbl, name FROM splits";

Once I select the checkboxes echoing $query gives:
DELETE FROM file.xls WHERE name=''

So that's mixed up.

Sorry to be a pain but nearly there.

[Roger_Ramjet]

Go back and read my posts, I've answered this twice. If you do you will see where I take it from the query results and concatenate it into a comma seperated string with the filename for the value of the checkbox.

[obrienkev]

Thanks ever so much Roger for your help.

Sorry for being a nuisance but I really appreciate all your help.

Thanks again.