[SOLVED] Reducing the Number of Queries

[NovaRising]

I'm trying to build a table with information from the database. I figure the best way to call this since I use the table several times for different user groups would be to build a function and pass values to it.

 

Basically I need to repeat what is below with different query requirements.

 

// Print pending...
	$sql_query = mysql_query("SELECT * FROM members WHERE authorized='NO'");

                if($sql_query > 0){
		$pending_data = mysql_fetch_array($sql_query, MYSQL_ASSOC);

                        printtable($pending_data, "Users waiting authorization");

	// Print current...
                }

 

The function I'm using (not quite finished) to print the table would be:

function printtable($array, $tablesummery){

$tablekey = array(
				  "firstname" => "First Name",
				  "lastname" => "Last Name",
				  "email" => "Email",
				  "phone" => "Phone",
				  "authorized" => "Approved",
				  "dateauthorized" => "Date Approved",
				  "admin" => "Administrator",
				  "dateapplied" => "Date Applied",
				  "notes" => "Notes",
				  );

echo '<table id="admintable" summary="'.$tablesummery.'">';

echo '<thead><tr>';

foreach($tablekey as $key => $value){

	echo '<th scope"col">'.$value.'</th>';

}

echo '</tr></thead>';

echo '<tbody>';

## NEED TO OUTPUT <TR> HERE

foreach($array as $key => $value){

echo '<td>'.$value.'</td>';

}

## NEED TO OUTPUT </TR> HERE

echo '</tbody></table>';
}

 

Usually I use some form of while statement and query within the while, however, doesn't that query the table every time? So if I have 50 results I would have queried the table 50 times? What is the best way to make the results an array without querying the table for every user like I usually do with a while statement?

 

$pending_data looks like this:

Array ( [id] => 20 [firstname] => Myname [lastname] => Lastname [username] => Quickthinker [password] => 12810dba6234443334dDDDFDF [email] => [email protected] [phone] => (453)-453-9874 [authorized] => No [admin] => No [dateapplied] => 1243223523 [dateauthorized] => [notes] => )

 

ofcourse, it only has one value...I'm thinking an array within an array so $pending_data passed through the function would look like $array[1][username], $array[1][password]

$array[2][username], $array[2][password]

 

etc...

 

 

[NovaRising]

And as you can tell, it would be necessary to repeat the <tr> tags for each user. I commented where they should be in the function but I'm not sure how to print them until I can figure out my array/query deal.

[Ken2k7]

I'm not following. Why do you need to repeat that numerous times?

[NovaRising]

I'd like to have:

 

##HTML Table A : Pending Users

 

##HTML Table B: Administrators

 

##HTML Table C: Authorized Users

 

To do that I'd only have to create a separate query for each one and send it to the table creating function. So this should require a total of three queries. I guess my question is, what is the best way to query the table once for each of those tables (because it would require three different criteria) and echo the results efficiently or send them to the function in the form of an array?

 

What I don't want to do is:

 

$query = mysql_query(criteria)

 

$results = count($query)

 

while ($i < $results){

$query2 = mysql_query(where userid=$i)

 

$user_info = mysql__fetch_array($query2, MYSQL_ASSOC);

 

echo '<tr><td>'.$user_info['username'].'</td><td>'.$user_info['password'].'</td><td>'.$user_info['email'].'</td></tr>';

 

$i++;

}

 

That would query the DB at least one time for every record right? What's a better way?

 

I'm assuming it would look like the following

 

array

[1] array

      [firstname] => value

      [lastname] => value

      => value

      [password] => value

[2] array

      [firstname] => value

      [lastname] => value

      => value

      [password] => value

[3] array

      [firstname] => value

      [lastname] => value

      => value

      [password] => value

 

 

[NovaRising]

Actually, I have it...

 

$sql_query = mysql_query("SELECT * FROM members WHERE authorized='NO' LIMIT 50");

 

 

while($row = mysql_fetch_array($sql_query, MYSQL_ASSOC)){

foreach ($row as $key => $value){

$pending_data[$i][$key] = $value;

}

$i++;

}

 

That way, when I pass $pending_data through to the function it is a double array.

 

That will only query the DB once right? And then I can use $pending_data all I want with all it's results?

[Ken2k7]

Just run 3 queries. That's not a lot. I wouldn't worry about it. Just write and run 3 separate queries. 3 isn't a lot and MySQL can handle it relatively fast.

[NovaRising]

Oh, I concur. 3 isn't many. Problem was is I used to run 50-100 in my attempt to generate a table of users...or more depending on the number of results. I'm a learning programmer, what can I say.

 

Topic Solved