php & mysql - how better could i right this?

[unwashed potato!]

at the moment this is the rough structure of my site.

1. first i include the files i need that connect to the db, have all the variables that will be used to query the database, such as the below, and then close the db connection

$result_comsingle = mysqli_query($con,"SELECT * FROM commercial WHERE comId = '$comId'");

2. i then have the header section of the site
3. and then the main content which will have lines like

   while($row = mysqli_fetch_array($result_stafftimes_repeat)) {
 

$comId =$row['comId'];
$frequency =$row['frequency'];
$amount =$row['amount'];


  while($rowcom = mysqli_fetch_array($result_comsingle)) {
	$comName = ucwords($rowcom['name']);
	$comColor = ucwords($rowcom['color']);
	
}	  
	
	$tableTimes .= "<tr><td>Location:".$comName."</td></tr>";
	$tableTimes .= "<tr><td>Frequency:".ucwords($frequency)."</td></tr>";

}

now this usually works fine when there is just one while loop. it grabs what i need and i do what i want with it. the issue i have is when i do the above and have a while loop inside a while loop as the second loop is returning nothing.

as best i can i've figured this out to be because of the way it's structured and the query line coming before the first while loop, so before the $comId is figured out, but i can't think how else to structure this so that the database query variables are in another file, able to be used by other pages.

i also understand i could join the 2 database queries into one using the join ability, but am lost how to do this. anyone able to simplify it and explain what i should be doing?

 

[Ashley Jordan]

You are only performing a single select on a single database.table, so there is no need to perform a join in mysql as you have all of your results in your SELECT *.

Is there any reason why you want to separate your results with $result_stafftimes_repeat and $result_comsingle?


I have a feeling your second while is failing as your results you are fetching is already being used in a loop. ( could be wrong ). Anyways, i would recommend the below:

while($row = mysqli_fetch_array($result_stafftimes_repeat)) {

	$comID		=$row['comId'];
	$amount		=$row['amount'];
	$comName	=ucwords($row['name']);
	$comColor	=ucwords($row['color']);
	$frequency	=ucwords($row['frequency']);


	$tableTimes	.= "<tr><td>Location:".$comName."</td></tr>";
        $tableTimes     .= "<tr><td>Frequency:".$frequency."</td></tr>";
}

 

[meandu229]

Unless you use all your columns you might aswell change the Select * from to select comid,amount,name,color,frequency,etc from.