Var output from MySQL Query to table

[7thgencivic_com]

I have a query that returns several columns.
I only want N columns in the table, but use the other cols somewhere else.

This is what I have now

	<?PHP
		while ($row = @mysql_fetch_row($result)) {
			echo "\n<tr onmouseover=\"this.style.backgroundColor=\'white\'\;this.style.cursor=\'hand\'\;\" onmouseout=\"this.style.backgroundColor=\'\'\;this.style.cursor=\'\'\;\">";
				foreach($row as $data)
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data </td>";
			echo "\n</tr>";
		}
	?>

BUt it spits out all the cols from the query. Just want selected ones.

Thanks,
Derek

[jonathen]

If I understand what you're saying, you only want columns that begin with N?

If so, you could use:
SELECT * FROM yourtable WHERE columnname LIKE 'N%'

That will pull all columns that contain values beginning with N.

[stolzyboy]

i think he means 'N' as in a number 'N', like 20 to the 'Nth' degree

[swon]

You can use the $row array for that:

<?PHP
while ($row = @mysql_fetch_row($result)) {
echo "\n<tr onmouseover=\"this.style.backgroundColor='white';this.style.cursor='hand';\" onmouseout=\"this.style.backgroundColor='';this.style.cursor='';\">";
echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $row[1] or $row[2] or $row[3]</td>";
echo "\n</tr>";
}
?>

[7thgencivic_com]

I don't want rows, but columns. I only want certain selected columns showing from the query.

something like this.

	<?PHP
		while ($row = @mysql_fetch_row($result)) {
			echo "\n<tr onmouseover=\"this.style.backgroundColor=\'white\'\;this.style.cursor=\'hand\'\;\" onmouseout=\"this.style.backgroundColor=\'\'\;this.style.cursor=\'\'\;\">";
				foreach($row as $data)
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['part_name'] </td>";
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['status'] </td>";
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['category'] </td>";
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['manufacturer'] </td>";
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['isOutNow'] </td>";
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['what_model'] </td>";
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['lowest_price'] </td>";
					echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['cdn_price'] </td>";
			echo "\n</tr>";
		}
	?>

[mikeatrpi]

<?PHP
    while ($data = @mysql_fetch_array($result)) {     //look here
        echo "\n<tr onmouseover=\"this.style.backgroundColor='white';this.style.cursor='hand';\" onmouseout=\"this.style.backgroundColor='';this.style.cursor='';\">";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['part_name'] </td>";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['status'] </td>";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['category'] </td>";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['manufacturer'] </td>";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['isOutNow'] </td>";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['what_model'] </td>";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['lowest_price'] </td>";
                echo "\n\t<td valign=\"top\" style=\"width:100\" class=\"widCell\"> $data['cdn_price'] </td>";
        echo "\n</tr>";
    }
?>

[7thgencivic_com]

ya, mine was just an example, i knew i had the wrong array
name.

But, I can't put single quotes here

$row['part_name']

cause it throws an error. When I remove the quotes, it seems to work and returns the correct amount of rows, but I get no data shown in the table cells for those array values.

Derek

[mikeatrpi]

Try this; watch the quotes:

echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $data['part_name'] .'</td>';

There a couple of different ways of doing it.

[7thgencivic_com]

ok, tried that, but still not getting the data that is there.

<tr onmouseover="this.style.backgroundColor='white';this.style.cursor='hand';" onmouseout="this.style.backgroundColor='';this.style.cursor='';">
	<td valign="top" style="width:100" class="widCell"></td>
	<td valign="top" style="width:100" class="widCell"></td>

<td valign="top" style="width:100" class="widCell"></td>
<td valign="top" style="width:100" class="widCell"></td>
<td valign="top" style="width:100" class="widCell"></td>
<td valign="top" style="width:100" class="widCell"></td>
<td valign="top" style="width:100" class="widCell"></td>
<td valign="top" style="width:100" class="widCell"></td>
</tr>

is what I get back, but as many as there are results. So i know it is getting the data, but where is it?

[mikeatrpi]

Everything looks okay to me; maybe you should remove the @ and see if it shows a warning.

Otherwise, check stupid things like variable names:
while ($data = @mysql_fetch_array($result)) { //look here
so use $data['part_name'] not $row['part_name']; (because $row would be empty.)

If you still can't get it, post more code.

[7thgencivic_com]

still no go.
here is most of the page, but just the relevant SQL and form.

<? if ($_REQUEST['web_task']) {
	$sql_where_clause = "0=0";
	$manufacturer_id = $_REQUEST['manufacturer_id'];
	$category = $_REQUEST['category'];
	$what_model = $_REQUEST['what_model'];
	$isOutNow = $_REQUEST['isOutNow'];		

if ($manufacturer_id) {
	$sql_where_clause .= " AND pl.manufacturer = $manufacturer_id";
}
if ($category) {
	$sql_where_clause .= " AND pl.category = $category";
}
if ($whatmodel) {
	$sql_where_clause .= " AND pl.what_model = $what_model";
}
if ($isOutNow) {
	$sql_where_clause .= " 	AND pl.isOutNow = $isOutNow";
}
	} else {
		$sql_where_clause = "1=0";
	}
//do query

$sql = ("SELECT pl.id,CASE WHEN date_add(pl.date_added,INTERVAL 14 day) >= current_date() THEN 'new' WHEN date_add(pl.date_updated,INTERVAL 14 day) >= current_date() THEN 'updated' ELSE '&nbsp;' END as status ,pl.part_name,CASE WHEN pl.manufacturer IS NULL THEN 0 ELSE pl.manufacturer END AS manufacturer_id,pl.isOutNow,pl.date_expected,pl.what_model,pl.category,pl.lowest_price,pl.cdn_price, pm.manufacturer as manufacturer,pc.category as category,pcm.model as what_model
FROM         parts_list pl LEFT OUTER JOIN
                      parts_manufacturer pm ON pl.manufacturer = pm.id INNER JOIN
                      parts_category pc ON pl.category = pc.id INNER JOIN
                      parts_carmodel pcm ON pl.what_model = pcm.id
	WHERE $sql_where_clause
	ORDER BY pl.category,pl.part_name");

$result = mysql_query($sql);
if (!$result) {
    	echo mysql_error();
	echo mysql_errno();
echo ("<p>SELECT pl.id,CASE WHEN date_add(pl.date_added,INTERVAL 14 day) >= current_date() THEN 'new' WHEN date_add(pl.date_updated,INTERVAL 14 day) >= current_date() THEN 'updated' ELSE '&nbsp;' END as status ,pl.part_name,CASE WHEN pl.manufacturer IS NULL THEN 0 ELSE pl.manufacturer END AS manufacturer_id,pl.isOutNow,pl.date_expected,pl.what_model,pl.category,pl.lowest_price,pl.cdn_price, pm.manufacturer as manufacturer,pc.category as category,pcm.model as what_model
FROM         parts_list pl LEFT OUTER JOIN
                      parts_manufacturer pm ON pl.manufacturer = pm.id INNER JOIN
                      parts_category pc ON pl.category = pc.id INNER JOIN
                      parts_carmodel pcm ON pl.what_model = pcm.id
	WHERE $sql_where_clause
	ORDER BY pl.category,pl.part_name");
	mysql_close();	
	exit;	
}
?>
<!--- output of query here --->
				<script language='javascript'>
				function Start(page) {
					OpenWin = this.open(page, 'CtrlWin', 'toolbar=no,menubar=no,location=no,scrollbars=yes,resizable=yes');
						}				
				</script>
				<style>
				.RecordListTable {table-layout : fixed;border-width:0;border-width:1;border-bottom-width:0;border-style : solid;}
				.RecordListButtonBar {text-transform:capitalize;background: buttonface; font: menu; border: 1px outset white;cursor: default; padding-top: 0; padding: bottom: 0;border-top: 1px solid buttonhighlight;border-left: 1px solid buttonhighlight;border-right: 1px solid buttonshadow;border-bottom: 1px solid buttonshadow;}
				.arrow {color: black; padding: 0; font-size: 10px;height: 11px; width: 10px; overflow: hidden;margin-bottom: 2; margin-top: -3; padding: 0; padding-top: 0; padding-bottom: 2;}
				.widCell {font-size:10px;height:11px;font-family:verdana;border-style : solid; border-width : 1;border-left-width:0;border-right-width:0;border-top-width:0;}
				.filObj {font-size: 10px; border-style : solid;border-width : 1;} 
				.handle {border-left: 1 solid buttonhighlight; border-right: 1 solid buttonshadow; border-top: 1 solid buttonhighlight; border-bottom: 1 solid buttonshadow;padding: 0; margin: 0; width: 3; height: 100%; overflow: hidden;}
				</style>

<table id=parts width="100%" class="tbFrame" cellSpacing=0 cellPadding=2>

	<thead>
		<tr>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">Part Name</td>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">Status</td>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">Category</td>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">Manufacturer</td>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">Avail.</td>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">Model</td>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">US $</td>
		<td class="RecordListButtonBar" onClick="sortColumn(event);" onMouseOver="this.style.cursor='hand';">CDN $</td>																					
		</tr>
	</thead>
	<tbody>
<?PHP
	while ($row = mysql_fetch_row($result)) {
		echo "\n<tr onmouseover=\"this.style.backgroundColor='white';this.style.cursor='hand';\" onmouseout=\"this.style.backgroundColor='';this.style.cursor='';\">";
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['part_name'] .'</td>';
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['status'] .'</td>';
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['category'] .'</td>';
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['manufacturer'] .'</td>';
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['isOutNow'] .'</td>';
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['what_model'] .'</td>';
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['lowest_price'] .'</td>';
				echo "\n\t" . '<td valign="top" style="width:100" class="widCell">'. $row['cdn_price'] .'</td>';
		echo "\n</tr>";
	}
?>
</tbody></table>

[mikeatrpi]

$row = mysql_fetch_row($result))

should be
$row = mysql_fetch_array($result))

Try that and see what happens. It should work since you said it outputs an empty table. Once you get some data you can go back to your foreach() bit from the first post. Just be warned, I tried the same thing once and foreach() messed up when my tables had some NULL values in them.

[7thgencivic_com]

sweet..thanks..it works now. I can't belive I didn't catch that. All the code starts to look the same after a while.

Derek