Adding another table from same database to exsisting result?

[JackS]

Well until today I had thought I was fairly capable at PHP when dealing with mySQL. However this one totally eludes me. Sufficed to say its probably so simple, but after 2 days of probably hundreds of attempts I am at a loss, and thus here to the pros to ask for help....

 

So here goes the problem. I have an exsisting code, which pulls data from a single table called data in an MYSQL DB using a search of that database. This way I can pull one record out of hundreds.

 

What I want to do is add data from a second table called level to be pulled at the same time, and added to my exsisting data. Yet for some reason I can not JOIN the tables in this manner. Nor have I been able to pull data from level and post it.

 

Example of exsisting code:

$search=$_POST["search"];

$result = mysql_query("SELECT avatar, name, req, male, FROM data WHERE name= '$search'");

//grab all the info
while($r=mysql_fetch_array($result))
{
   $avatar=$r["avatar"];
   $name=$r["name"];
   $req=$r["req"];

   //display the row
   echo "<strong>Player ID:</strong> $avatar <br> <strong>Player Name:</strong> $name <br>";}
switch ($req){
case "0";
echo "<strong>Req:</strong> 1 <br>";
}
break;
case "1";
echo "<strong>Req:</strong> 2 <br>";
}
break;
case "2";
echo "<strong>Req:</strong> 3 <br>";
break;
}
?>

 

Basically what I want to do is, grab one colum of data from level and add it to the end, later I may wish to add additional colums or more tables from this DB, but I figured if I can get one, the rest would be easy.

 

So anyone got any thoughts on this?

[dropfaith]

can you post your table structure for both tables so we know what fileds there will be linked from

 

is there any ids or anything

[JackS]

Hope the below is what your looking for... not sure how much information your going to need.

 

 

Data:

avatar

name

pro

(additional data present, but not going to be used.)

 

 

Level:

avatar

credits

(additional data present, but too numerous to list more then needed. LOL)

 

All tables contain the AVATAR information, which is basically an ID# which associates all the tables in this DB to the actual program.

 

I had originally tried to use JOIN [database name] WHERE or ON data.avatar == level.avatar and avatar == avatar, but as I said above the JOIN command seems to fail, or at least not produce results.

 

If the above table info is not sufficent please let me know, I will see if I can generate a more detailed result.

[zenag]

select * from table1 join table2 ON table1.fieldname = table2.fieldname

[beansandsausages]

i would also sanatize your search?

 

$search=$_POST["search"];

[JackS]

select * from table1 join table2 ON table1.fieldname = table2.fieldname

 

Not to be a bonehead here Zenag, but well I have tried that. Either my code was bad, or... my attempt to generate results code was bad. Its possible my concept of JOIN is flawed as well. LOL....

 

So while that tid bit may help in some cases I think in this case I need a more lengthy example code to go by.

 

would also sanatize your search?

 

Code: [select]

$search=$_POST["search"];

 

Benn considering replacing the search code with something more useful, such as a partial word search code or wildcard search code, however thats after the initial program has something more to search for. LOL, as it stands now it works, while not pretty thats better then it not working.

[beansandsausages]

people will be able to insert mysql and java in your strings if un protected.

 

$search =  trim(htmlspecialchars($_POST['search'], ENT_QUOTES));

 

May not be 100% safe but its better than not being safe at all.

[GingerRobot]

We'll need to see the code you're trying to use but isn't working in order to help. And we'll need a bit more of an explanation of what didn't work.

 

And burnside: Use mysql_real_escape_string - that's what it's there for.

[JackS]

Ok, while I did not save the multiple ones that did not work, its fairly easy to show one that I was certain would work but did'nt.

 

$search=$_POST["search"];

$result = mysql_query("SELECT data.avatar, data.name, data.req, level.avatar, level.credits FROM data JOIN level ON level.avatar == data.avatar WHERE name= '$search'");

//grab all the info
while($r=mysql_fetch_array($result))
{
   $avatar=$r["avatar"];
   $name=$r["name"];
   $req=$r["req"];
   $credits=$r["credits"];

   //display the row
   echo "<strong>Player ID:</strong> $avatar <br> <strong>Player Name:</strong> $name <br>";}
switch ($req){
case "0";
echo "<strong>Req:</strong> 1 <br>";
}
break;
case "1";
echo "<strong>Req:</strong> 2 <br>";
}
break;
case "2";
echo "<strong>Req:</strong> 3 <br>";
}
break;
echo "<trong>Credits:</strong> $credits";
}
?>

[GingerRobot]

Aside from the fact that you've not told us what happens, what doesn't work etc, MySQL uses a single equals sign for equality, not a double.

[JackS]

Ahhh... well thats most helpful, and seems to produce no errors.

 

$search=$_POST["search"];

$result = mysql_query("SELECT data.avatar, level.avatar, data.name, data.req, level.credits FROM avatar JOIN level ON level.avatar = data.avatar WHERE data.name= '$search'");

//grab all the info
while($r=mysql_fetch_array($result))
{
   $avatar=$r["avatar"];
   $name=$r["name"];
   $req=$r["req"];
   $credits=$r["credits"];

   //display the row
   echo "<strong>Player ID:</strong> $avatar <br> <strong>Player Name:</strong> $name <br>";}
switch ($req){
case "0";
echo "<strong>Req:</strong> 1 <br>";
  }
break;
case "1";
echo "<strong>Req:</strong> 2 <br>";
  }
break;
case "2";
echo "<strong>Req:</strong> 3 <br>";
  }
break;
echo "<strong>Credits:</strong> $credits <br>";
}
?>

 

Now the only problem I have seems to be its not returing anything when I run a search.... No errors, but no values either. Any clues?