HELP!!! With optimizing mySQL statement

[eliZZZa]

Hi php freaks,

I am addicted, but unfortunately no pro yet.

So I kindly ask for your help.

I wrote a mySQL statement, which works, but I am sure, there is a more efficient way to write this. Could you please help?

 

//Request crime_country

$personid = $myrow["id"];

$countryid = mysql_query("SELECT destinationid FROM crime_countries WHERE sourceid = $personid");

$country = mysql_fetch_array($countryid);

$countryshort = mysql_query("SELECT shortname FROM countries_all WHERE id = $country[destinationid]");

if ($countryshort != NULL) {

$countryshortarr = mysql_fetch_array($countryshort);

printf ("<div id=\"flag\"><img src=\"http://www.crimeZZZ.net/images/flags/%s.gif\" /></div>\n", $countryshortarr["shortname"]);

}

//Ende Request

 

Thank you in advance and kind regards from Austria

eliZZZa

http://www.crimeZZZ.net/

A sample listing using the above statement can be viewed under

http://www.crimeZZZ.net/serialkiller_index/xtemplate_a.php

[shamilton]

Well you can do this:

 

SELECT 
crimeC.destinationid, allC.shortname
FROM
crime_countries crimeC
JOIN countries_all allC ON crimeC.destinationid = allC.ID
WHERE crimeC.sourceid = $personid

 

I think that is right although I haven't tested it...

 

[Barand]

As it starts with

 

$personid = $myrow["id"];

 

it looks like there is potential for another join in there to bring in the original table containing "id"

 

[eliZZZa]

Well you can do this:

 

SELECT 
crimeC.destinationid, allC.shortname
FROM
crime_countries crimeC
JOIN countries_all allC ON crimeC.destinationid = allC.ID
WHERE crimeC.sourceid = $personid

 

I think that is right although I haven't tested it...

 

 

Hi Shamilton,

first of all, great thanks for your prompt reply.

 

I don´t get it running (sorry for being sooo dummy  :'(

I wrote the following (did I get it right?):

 

$personid = $myrow["id"];

$countryshort = mysql_query("

SELECT

crimeC.destinationid, allC.shortname

FROM

crime_countries crimeC

JOIN countries_all allC ON crimeC.destinationid = allC.ID

WHERE crimeC.sourceid = $personid");

if ($countryshort != NULL) {

  printf ("<div id=\"flag\"><img src=\"http://www.crimeZZZ.net/images/flags/%s.gif\" /></div>\n", $countryshort["shortname"]);

  }

  else {

  printf ("\n");

  }

[eliZZZa]

As it starts with

 

$personid = $myrow["id"];

 

it looks like there is potential for another join in there to bring in the original table containing "id"

 

 

Hi Barrand,

thank you for your reply too - it sounds promising, but I don´t know, how to use your comment  ???

 

eliZZZa