[SOLVED] What is wrong with this SQL statement?

gnawz · October 10, 2008

The SQL statements are not working. It returns a page with only titles and no database results..

First file

<?	
require_once '../../functions.php';
?>
Select category to print
<hr>
<form name="frmPrint" action="printstocksheet.php">
<table width="100%" border="0" align="center">
          <tr>
            <td width="128">
            <select name="Category">
                <option value="0">Select Category</option>
                <?
		$sql = "SELECT DISTINCT Category FROM cstockitems ORDER BY Category ASC";
		$result = dbQuery($sql);		

		if(dbNumRows($result))
		{
			while($row = dbFetchAssoc($result))
			{
			echo "<option>$row[Category]</option>";
			}
		}
		 		else 
				{
			echo "<option>No Categories Present</option>"; 
				}
  ?>
              </select></td>
            <td width="31"><input type="submit" name="submit" value="Go" class="button_image" onClick="return CheckShowBrandByCategory();"></td>
            <td>
              <input type="submit" name="submit" id="button" value="Back" onClick="window.location.href='index.php';" class="button_image">
            </td>
          </tr>
        </table>
</form>

Second file

<?
require_once '../../functions.php';

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1209/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>STOCK FORM</title>
<script language="JavaScript">
var gAutoPrint = true; // Flag for whether or not to automatically call the print function

function printSpecial()
{
if (document.getElementById != null)
{
	var html = '<HTML>\n<HEAD>\n';

	if (document.getElementsByTagName != null)
	{
		var headTags = document.getElementsByTagName("head");
		if (headTags.length > 0)
			html += headTags[0].innerHTML;
	}

	html += '\n</HE' + 'AD>\n<BODY>\n';

	var printReadyElem = document.getElementById("printReady");

	if (printReadyElem != null)
	{
			html += printReadyElem.innerHTML;
	}
	else
	{
		alert("Could not find the printReady section in the HTML");
		return;
	}

	html += '\n</BO' + 'DY>\n</HT' + 'ML>';

	var printWin = window.open("","printSpecial");
	printWin.document.open();
	printWin.document.write(html);
	printWin.document.close();
	if (gAutoPrint)
		printWin.print();
}
else
{
	alert("Sorry, the print ready feature is only available in modern browsers.");
}
}

</script>

<style type="text/css">

.lines td {
border: #666666 solid 1px;
}

</style>
</head>

<body>

<div id="printReady">

<table width="100%" border="0" class="lines">
  <tr>
    <td colspan="2"><div align="center"><strong></strong></div></td>
<?
$thismonth = mktime(0,0,0,date("m"),date("d"),date("Y"));

$nextmonth = mktime(0,0,0,date("m")+1,date("d"),date("Y"));

$montheafternext = mktime(0,0,0,date("m")+2,date("d"),date("Y"));

?>
    <td colspan="5"><div align="center"><strong><? echo date("F", $thismonth); ?></strong></div></td>
    <td colspan="5"><div align="center"><strong><? echo date("F", $nextmonth); ?></strong></div></td>
    <td colspan="5"><div align="center"><strong><? echo date("F",$montheafternext); ?></strong></div></td>
  </tr>
  <tr align="center">
    <td width="250"><em><strong>Brand</strong></em></td>
    <td width="15"><em><strong>Qty</strong></em></td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15"><em><strong>Bal</strong></em></td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15"><em><strong>Bal</strong></em></td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15">Day</td>
    <td width="15">Sold</td>
    <td width="15"><em><strong>Bal</strong></em></td>
    </tr>
<?


$Cat = '';

if (isset($_POST['Category'])) $Cat = $_POST['Category'];

$sqlTitle = "SELECT * FROM fragrancestock WHERE Category = '$Cat' ORDER BY Brand ASC";

$result = dbQuery($sqlTitle);

if (dbNumRows($result) > 0) {
	$i = 0;

	while($row = dbFetchAssoc($result)) {
		extract($row);

		if ($i%2) {
			$class = 'row1';
		} else {
			$class = 'row2';
		}

		$i += 1;
?>
  <tr class="<?php echo $class; ?>">
    <td width="250"><?php echo $Brand; ?></td>
    <td width="15"><?php echo $Quantity; ?></td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    <td width="15"> </td>
    </tr>
  
     <?php
} // end while


?>
</table>
</div>
<table width="100%" border="0">
  <tr>
    <td></td>
  </tr>
           <?php	
} else {
?>
  <tr>
    <td> </td>
  </tr>
  <tr>
  No stock items yet</td>
   <?php
}
?>
    <td> </td>
  </tr>
</table>

<a href="javascript:void(printSpecial())">Print</a> 
</body>
</html>

When I remove the WHERE clause from the second file, it works well.

What is the problem here?

Maq · October 10, 2008

Before examining your entire code can you add this at the end of your sql statements:

or die(mysql_error());

R0bb0b · October 10, 2008

where do you define the function "dbNumRows()". I have never seen it in the manual.

Maq · October 10, 2008

It's not in the manual. I think what he meant was "db2_num_rows()" which returns the number of rows that have been affected by like an insert, delete etc... But that is still incorrect, I think you want to use the function "mysql_num_rows()"?

gnawz · October 11, 2008

Guys..

I have a functions file which I include in every page....

The following are my db functions

<?
$dbConn = mysql_connect ($dbHost, $dbUser, $dbPass) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($dbName) or die('Cannot select database. ' . mysql_error());

function dbQuery($sql)
{
$result = mysql_query($sql) or die(mysql_error());

return $result;
}

function dbAffectedRows()
{
global $dbConn;

return mysql_affected_rows($dbConn);
}

function dbFetchArray($result) 
{
return mysql_fetch_array($result);
}

function dbFetchAssoc($result)
{
return mysql_fetch_assoc($result);
}

function dbFetchRow($result) 
{
return mysql_fetch_row($result);
}

function dbFreeResult($result)
{
return mysql_free_result($result);
}

function dbNumRows($result)
{
return mysql_num_rows($result);
}

function dbSelect($dbName)
{
return mysql_select_db($dbName);
}

function dbInsertId()
{
return mysql_insert_id();
}
//End DB functions
?>

R0bb0b · October 11, 2008

to rule them out completely, I would temporarily adjust the following function to echo the query and then paste it into your db manager query window.

<?php
function dbQuery($sql)
{
   echo "<br />" . $sql . "<br />";
   $result = mysql_query($sql) or die(mysql_error());
   
   return $result;
}
?>

gnawz · October 12, 2008

Thanks guys!

It was not picking the value. I had to change my SQL statement on my dynamic drop down to ORDER BY not GROUP BY

Maq · October 12, 2008

Ah, ok, of course you would!

Sign In

[SOLVED] What is wrong with this SQL statement?

Recommended Posts

gnawz

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

R0bb0b

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

gnawz

Link to comment

Share on other sites

R0bb0b

Link to comment

Share on other sites

gnawz

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information