SQL while loop in a SQL while loop.

joshd963

Hi guys...

I have a problem with a script i am making.

Currently, i have a while loop that pulls records out of a database.

on each record, i wanna do a query to grab info from another table, using a field from that record to search the other table with .. ie ...

// This is the first query that grabs all my records
$sql="SELECT * FROM port WHERE NAME LIKE '".$q."%'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
// and then here, on each record, i want to run another query that looks for the //'LINER' on the other table, and return records from the other table. 
$test1 = $row['LINERS'];
$test2 = explode(' ', $test1);
foreach ($test2 as $test3){

// this is the area i am having trouble with. it doesn't like running another query //within the existing one, it seems. 
$test4="SELECT * FROM liners WHERE LINER LIKE '".$test3."'";
$result = mysql_query($test4);
while($test5 = mysql_fetch_array($test4))
{ 
echo "".$test5['LINER']."";
}
}
}

so i was wondering if you guys have any ideas...
Many thanks!

bradgrafelman

The one idea I have is: don't do it. Sounds like you'd benefit from a simple JOIN instead.

Also, another idea I had: indent your code! If you really expect others to even look at your code longer than a few seconds (let alone try to read/understand it and then make changes/suggestions), utilizing whitespace consistently and effectively is simply a requirement IMHO.

cretaceous

the actual problem is that your second query result is put into a variable named $result
but that is the exact same name of your first query result...

so change this:

$test4="SELECT * FROM liners WHERE LINER LIKE '".$test3."'";
$result = mysql_query($test4);
while($test5 = mysql_fetch_array($test4))

$test4="SELECT * FROM liners WHERE LINER LIKE '".$test3."'";
$result4 = mysql_query($test4);
while($test5 = mysql_fetch_array($result4))

as Brad suggests a join would be better
and don't blame me for your very confusing naming system - name your steps in the process with more meaningful names - e.g something like:
$sql1, $sql2, $sql3 - the querystrings
$result1, $result2 etc - the response arrays
$test1, $test2 etc - your exploded items

joshd963

many thanks for your swift replies. I apologize for my messy code, i was just experimenting and so i didn't indent / name my variables.

the join function is what i was thinking of. I figured this sort of thing probably exists, but i was having a hard time finding out about it.

The problem is, i'm not sure if it will be adequate in my circumstances, having researched the function. If you'll allow me to explain a little..

In my database I have three tables.
Table one - a list of destinations and companiess that represent them.
Table two - a list of the companies.
Table three - a list of personnel.

Table one: port

| NAME | COUNTRY | LINERS |
|______ |_____|__|
| Aden | Yemen | TC EF |
|___ |_____|_____|

Table two: liners

| ID | LINER | LINE_MANAGER | EMAIL |
|_ |_______|_______|________|
| TC | Test Company | MK | admin@tc.com |
| EF | Eastern Freight | JB | admin@ef.com |
| |_________|_______|___________|

Table three: people

| ID | NAME | EXTENSION |
|__|_______|____|
| MK | Mike Karachi | 111222 |
| JB | Joe Bloggs | 333444 |
||________|_______|

Now currently, i have a ajax instant search script. so if i type "aden" it'll query the ports table like this.
[INDENT]"SELECT * FROM port WHERE NAME LIKE %$search%"[/INDENT]
This bit works great. it does a while loop and returns any records with 'aden' in it.

But now, on each record that is returned, i would like to do another query. This is where i'm having the problem.

So let's say i've got my port of Aden record. In the liners field, i have TC EF.
I now perform an explode function on the field, seperated by a space:
[INDENT]$linernames = explode(" ", $row['LINERS'])[/INDENT]

This also works fine. But now, i want to perform an SQL query on each record returned (there may be more than just the one 'Aden', and they may have different data in the 'liners' field.), using this information i just got, within the new query... for example.. (i know this code wouldn't work in it's current form.. i'm hoping that you can understand my logic if i write it this way though.)
[INDENT]

foreach($linernames as $linerID){
    $linerSQL = "SELECT * FROM liners WHERE ID=$linerID";
    $linerRESULT = mysql_query($linerSQL);
          while($linerROW = mysql_fetch_array($linerRESULT))
          {
                   echo $linerROW['LINE_MANAGER'];
          }
}

[/INDENT]

So in theory... i would be:
- grabbing the port i searched for from the database.
- Looking at the liners field on the port record.
- blowing that data up into an array, by exploding it with the space character.
- doing a query on each object in this array.

I'm not sure if this is possible with the join function, since i am only getting the list of liners AFTER i have done the initial query, and performed the explode function.

I'm wondering if you guys had any ideas.

I would like to thank you in advance for taking the time to read this, and for any possible contribution you may have.

Regards,
Josh

cretaceous

you should really have one more table that relates PORTS to the LINERS that represent the port

that would only be pairs of 'id' values:
e.g
port id : liner id
123 : 456

then you wouldn't need the nasty column in your Ports table where you crunch together the Liners

and as a result you wouldn't need the 'explode'.. and you could then do everything with JOINS - this may be more work to fix right now, but you will thank yourself in the future

joshd963

Hi cretaceous, thank you for time taken to reply.

I am a little confused by what you mean. I am sure it makes sense, but i'm having trouble comprehending it! Lol.

having looked at what you have written (i think i am wrong though) it looks like, with your idea, i will only be able to assign one 'liner' to each 'port'. However, sometimes i could have all four liners on the same port, or maybe just one.
I'm sure that's not the case with what you have just explained to me, i'm just having a hard time understanding it! brain is a bit mushy today, hehe.

so, in your case, if i had
TABLE: RELATIONS

i would like.. enter the port once if there was just one liner, or four times if there were 4 liners? Like so..

|PORTID | LINERID|
|01 |2|
|02 |1|
|02 |3|
|02 |4|

cretaceous

yeah that's right ..

(you can add a unique identifier column for the table, but it shouldn't be needed)

then when you do joins ... LEFT JOIN in this case...
mysql will give you the port AND all the matching liners at that port
that's really the whole point of relational databases
- it will give you the matches you want and multiple variations of that, when you need them

just trust the (sql) force !

it will probably take a while to figure out JOINS but stick with it

joshd963

Hi Cretaceous, thank you for your reply.
I've tried my best to do this JOIN thing, i think i understand it now.

What i did was, for every port in my table with more than one liner, i duplicated it... for example..

Aden | Yemen | SLF TMCL

Became

Aden | Yemen | SLF
Aden | Yemen | TMCL

I then created the following SQL code to join the liners to the ports.


$sql = "SELECT PKEY, NAME, COUNTRY, LINERS, LINER, LINE_MANAGER FROM port LEFT JOIN liners ON port.LINERS=liners.ID";
	$result = mysql_query($sql);
		while($row = mysql_fetch_array($result))
			{
				var_Export($row);
				echo "<br><br>";
			}

to me this looks fine, however for every port that has more than one liner, it is only displaying the first record. So instead of having:

Aden | Yemen | SLF
Aden | Yemen | TMCL

I'm just getting:

Aden | Yemen | SLF

I have double checked my code, and experimented with different types of join, but i just can't get it to work. I tried adding a primary key on the port table, but that didn't work either.

I would be grateful for a second pair of eyes.

I appreciate your help, thanks in advance,

Josh

cretaceous

did you try adding the relationship table?
You shouldn't be duplicating the PORT entries - baaad idea!

you need to store things in their finest level of detail (referrred to as atomic) and you then use SQL code to combine liners, ports and managers with JOINS

I think you may need to find a tutorial to get on top of these points - otherwise you could be going in circles

joshd963

yep i understand you, i think.
there are not any duplicate records, the name and country may be same, but the liner is different.
Which is why im a little confused as to why i should have another table for relations (i don't doubt your logic, i'm just struggling to understand).

I do understand atomic values etc.. But i thought this was atomic.

i thought the relations table would just be duplicating information i already have...

IE:

table: relation
1 | 1
1 | 2
2 | 2

So i dont really understand... instead of having "Aden" and "SLF", i have "1" and "1". It is confusing me a little as there will be no duplicate records in the current state.

I'm gonna build a relation table tomorrow and give it a go anyways, maybe it's doing something i don't quite understand.

Best Regards
Josh

cretaceous

well - think of it terms of the real world

there are ports, liners and managers
each should be in a separate table (they are different things in reality)

then you use another table to say who matches up with who - so :
Port number 1 matches with Liner number 7
and
Port number 1 matches with Liner number 15
and
Port number 1 matches with Liner number 23
etc ..

yes it can get a bit abstract !
you could have done it the way you started out, but it will not be flexible and can make things very difficult later on