Query Advice needed

[dwighty]

Hi Guys,

Not sure how to do this really so was looking for a few pointers.

Situation is that I run a query to get data from table1. This data is for the header info for the data table.

The next query I need to run is from about sql table using the data received from the first query.

so,
$query1 = "SELECT * FROM table1 WHERE ..."
{ returns for eg. section, title, active }
Then in query2
$query2 = "SELECT section, title, active FROM table2 WHERE ..."

The issue is that I do not know what columns to select in query2 as this depends on the results from table1.
Make sense?

I am assuming that I may need to produce some sort of array but not sure how.

Thanks

[IndiaMaster]

Hi dwighty,

I also faced this problem while working in a dataentry project.

I solved it as follows:

$query1="select * from table1 where...";
$rs1=mysql_query($query1);
$fields="";
while($row=mysql_fetch_array($rs1))
{
$fields=$fields.$row["field_name"].",";
}

//For truncating the last comma. If you have any other idea about this plz let me know
$fields=substr($fields,0,strlen($fields)-2);

$query2="select ".$fields." from table2 where...";

Hope this will solve your problem.

[d40sithui]

dwighty, your question is a little bit confusing. from my understanding, have you tried to use the function mysql_fetch_field() to get the column name(s) that you get from query 1, and then from there you extract the array into comma delimited string and use it for query2.

i guess something like this:(havent tested)

<?
$query1 = "SELECT * FROM table1 WHERE ..." // returns for eg. section, title, active

$result = mysql_query($query1);

for($i=0; $i<mysql_num_fields($result); $i++){
$column = mysql_fetch_field($result, $i);
echo "Column name:".$column->name."<br>\n"; //prints column name
}//ends for
?>

more info here:
[us2.php.net...]
[us2.php.net...]

[dwighty]

HI guys,

thanks for your answers, if have got part of the way there now. below is what i currently have.

## CODE ##
$sql4 = "SELECT * FROM ".$site_prefix."_table1 WHERE mod_id='$moduleid' and order_by_link='true' ORDER BY field_order ASC";
$result4 = mysql_query($sql4) or die("Error: " . mysql_error());

$fields = "";
$table = "";

while($tableheading = mysql_fetch_array($result4)) {
?><th><?php echo $tableheading['label'];?></th><?php
$fields=$fields.$tableheading["column"].",";
$table=$tableheading["table"];
}

$fields=substr($fields,0,strlen($fields)-1);
$orderby = $module['order_by'];
$sql5 = "SELECT id,".$fields." FROM ".$site_prefix."_".$table." ORDER BY ".$orderby." ASC";
$result5 = mysql_query($sql5) or die("Error: " . mysql_error());

for( $i = 0; $i < $tabledata = mysql_fetch_array($result5); $i++){
?>
<tr class="<?php echo row_color($i)?>">
<td><a href="/sm/section=<?php echo $sectionid;?>&amp;module=<?php echo $moduleid;?>&amp;func=edit&amp;id=<?php echo $tabledata[0];?>">edit</a></td>
<?php
foreach($tabledata as $row){
?>
<td><?php echo $row;?></td>
<?php
} // Close foreach
} // Close for
?>

## END OF CODE ##

My problem is that I do not want the foreach loop to display $tabledata[0] (first return in the array) but i do want to return the rest of the array, but i do not know how many returns there may be.

At the moment it is returning $tabledata[0] but it is also duplicating each column twice.

Not sure if I have explained that any better!

P/

[eelixduppy]

Is there a reason for selecting the ID from the table if you aren't going to be using it here? This would eliminate the need to remove the displaying of it in the loop.

[dwighty]

The id is being used in the line prior to the foreach loop. It gets the id of the row from the table2 so when i get round to editing/deleting that row it retrives the next stage by id.

[eelixduppy]

That works out nicely, then. Replace your foreach loop with the following:

unset($tabledata[0]);
echo '<td>'.implode('</td><td>',$tabledata).'</td>';

[dwighty]

Thats almost there now, but for some reason the results seem to be duplicating itself.

so it is printing

id, page_title, page_title, section_id, section_id, active, active

instead of page_title, section_id, active

[eelixduppy]

took me a few minutes to think why this is happening. It is because you are using

mysql_fetch_array

.

Replace:

[url=http://www.php.net/mysql-fetch-array]mysql_fetch_array[/url]($result5)

with this:

[url=http://www.php.net/mysql-fetch-row]mysql_fetch_row[/url]($result5)

[dwighty]

Oh bugger!

I was meaning to change that as well!

Thanks eelixduppy.