PHP/MySQL Query Assistance

[Highland]

Hi all,

I'm looking for some assistance with the below:-

I currently have Table A and Table B; within Table A I have a collumn that contains "35 65 79", for example, the numbers contained therein are each unique rows on Table B. I'm needing to create a method of querying Table B for each of the aforementioned, the quantity of the numbers contained in Table A's columns can vary so using string[0] string[1] etc is not reliable, I've also explored the use of explode and have had little succes.

Please see the below code I'm currently working with:-

$TableAQuery = mysql_query("SELECT * FROM `TableID` WHERE `Column` = '1'") or die(mysql_error());  

while ($TableAResult = mysql_fetch_array($TableAQuery)) {

foreach ($TableAResult["UniqueID"] as $value) {

$TableBQuery = mysql_query("SELECT * FROM `TableB` WHERE UniqueID = '".$value."'") or die(mysql_error());  

$TableBResult = mysql_fetch_array($TableBQuery);


echo $TableBResult["Name"];

}

Thanks in advance !

[pbismad]

Your database table with the list of values stored in one column is not 'normalize'. You can do a web search on database normalization to find what the means.

You would never store a list of values in one column. You would store a separate row for each of those values instead. Once you make this change to your database table design, you can write one JOIN'ed query that gets all the data you want, then simply loop over that data.

Next, the php mysql statements are obsolete and have been removed from php. If you are taking a programming course or learning on your own, you are learning out of date information. You should instead be using the php PDO statements and you should be using a prepared query to supply data to the sql query statement. You can research the php.net documentation to find out what PDO and prepared queries are.

Lastly, your database tables, columns, and php variables should have meaningful names so that anyone looking at the code and queries can deduce what they are doing. This is especially important if you are asking someone else to look at your code, such as when you post it in a programming help forum.

[maxxd]

Beyond what pbismad has pointed out, you never want to do queries in a loop. If you find yourself doing that, look at your query and rework it with joins. Part of reworking the query will be to select the specific columns you need; don't just 'SELECT *' - it's inefficient and not as easy to read when you come back to the code later for debugging or modification. Finally, you'll only want to print the error messages to screen in a development environment. Doing so on a live server can give a malicious user information about your database and the server upon which it runs.

[Highland]

Hi, thank you.

Further to your repsonses I have reviewed PDO queries, however the query I have below only seems to return a result if the query is in row one of the table, I would be grateful for any advice:-

			$sql = "SELECT DrinkID, DrinkName, DrinkURL, DrinkType, DrinkDescription, DrinkInstructions, DrinkIngredients, DrinkKeyWords, BrandDrinkName, BrandDrinkURL
			FROM Drink
			RIGHT JOIN Connection
			USING (DrinkID)
			LEFT JOIN BrandDrink
			USING (BrandDrinkID)
			WHERE DrinkURL = '".$Page_Requested."'";
			$stmt = $link->prepare($sql); 
			if($stmt->execute(array($_POST['DrinkID']))){ 
                                    // Work with result
                        }

Many Thanks