Running a select on a select

Clarkinator

I need information from 2 tables to insert it into a 3rd. What I have is the following:

if($_POST['report']){
	$con = mysql_connect($host, $user, $pass);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
	mysql_connect($host, $user, $pass) or die("Cannot connect: ".mysql_error());

After this, I have select query 1:

$query = "Select * from something WHERE something='"..$variable.."';";
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_array($result))
	{
         $variable2 = $row['row1']
         $variable3 = $row['row2']
         }

Directly after that, we can basically duplicate it but its searching for one of the declared variables in the while statement in another table.

Then it goes and inserts something into a 3rd table. When I add in the 2nd and 3rd SQL statements, I get 500 errors and I don't know whats going wrong.

Crowly

I think you can do it this way, the join may need tweaking

INSERT INTO table3 (fields)
(SELECT fields
 FROM table1 t1
 INNER JOIN table2 t2 ON t1.id=t2.id
 WHERE something)

Without anything more concrete its hard to come up with a suitable solution.

Clarkinator

But thats the problem. I'm inserting into 1 table thats different from the first 2.
Also the 2nd search runs off a term found in the first search.

Clarkinator

After swapping both SELECT queries for a SELECT INNER JOIN, I found the 500 error in the INNER JOIN query. I don't know how to fix it.

SELECT acqua_chars_old.characters.name, acqua_chars_old.characters.acct, logon.accounts.acct, logon.accounts.lastip FROM acqua_chars_old.characters FULL JOIN logon.accounts ON acqua_chars_old.characters.acct=logon.accounts.acct WHERE name='"..$char.."';

Crowly

name='"..$char.."'

This is wrong at least, the concatenation operator is . not ..
And please put some line breaks in the sql to make it more readable 🙂 Aliases on the tables will also help, and on the fields named where there are two or more equally named fields in different tables.

SELECT c.name, c.acct old_acc, a.acct new_acc, a.lastip 
FROM acqua_chars_old.characters c
FULL JOIN logon.accounts a ON c.acct=a.acct 
WHERE c.name='".$char."';

I think it should be possible with the use of sub queries, something along these lines

INSERT INTO table3 (a,b,c)
VALUES (1,
        (SELECT t2.value FROM table2 t2 WHERE something),
        (SELECT t1.value FROM table1 t1 WHERE something)
       );

Clarkinator

The 2nd SELECT query is running off a value found in the first query. Because of this, I can't include the variable in the WHERE clause.
The 1 "." doesn't help get rid of this stupid 500 error either.

Crowly

INSERT INTO table3 (a,b) 
VALUES (1, 
        (SELECT t2.value FROM table2 t2 
         WHERE something = (SELECT t1.value FROM table1 t1 WHERE something)), 
       );

These sql's are just samples of methods you can use, to give you some idea of how it can be done with one sql instead of several. There might also be other ways of doing it than my examples here.

It would be a lot easier if you could include some sample data, and wanted output. And the actual error message and the code where there error is triggered, and some lines above.

Clarkinator

Well, the first field is gathered from a cookie. Its a name.
2nd field is also a name gathered out of the database or one of the 2 textboxes.
3rd field is also a name and is linked to the 2nd field in the database on another table though.
4th field is an IP which is gathered from the same row as the 3rd field.
5th field is a string of text that is gained from a textbox on my page.