Programming Forums > Web Development and Design > PHP

k4pil · Feb 7th, 2006, 5:39 PM

Need some help with outputting from database.

The code im using is;

query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'";

The output is ;

(Toggle Plain Text)

<?
$i=0;
while ($i < $num) {

$prodID=mysql_result($result,$i,"productID");
$prodName=mysql_result($result,$i,"prodName");
$prodDesc=mysql_result($result,$i,"prodDesc");
$supplier=mysql_result($result,$i,"supplier");
$cost=mysql_result($result,$i,"cost");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td>
</tr>

<?
$i++;
}

However, the output is not the data being searched. Everything from the database seems to output from after the data being searched.

Hope this makes sense.

Any help

Thanks in advance

k4pil · Feb 7th, 2006, 6:23 PM

Need some help with outputting from database.

The code im using is;

(Toggle Plain Text)

query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'";

The output is ;

(Toggle Plain Text)

<?
$i=0;
while ($i < $num) {

$prodID=mysql_result($result,$i,"productID");
$prodName=mysql_result($result,$i,"prodName");
$prodDesc=mysql_result($result,$i,"prodDesc");
$supplier=mysql_result($result,$i,"supplier");
$cost=mysql_result($result,$i,"cost");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td>
</tr>

<?
$i++;
}

However, the output is not the data being searched. Everything from the database seems to output from after the data being searched.

I tried

(Toggle Plain Text)

$trimmedProdName = trim($prodName);

because i thought maybe it was the entries with empty data fields...but it dosn't help.

Hope this makes sense.

Any help

Thanks in advance

DaWei · Feb 7th, 2006, 6:47 PM

You're really a little short of information. Without knowing what your database contents are like and exactly what you are expecting, one can't even tell if your query should be ORs or ANDs. There's no indication of where $num comes from. Stuff like that. Personally, I'd probably fetch an entire row into an array and build the table with that, but I guess that's just me. If you aren't sure what's coming back as the result, you might sprinkle a few echoes or print_rs around. You don't show the actual query, so we can't tell if you're prepared to deal with failure or if you're prepared to display any error messages or warnings. You might try running the query by hand directly with MySQL and seeing what it spits back at you.

Infinite Recursion · Feb 7th, 2006, 9:20 PM

How are you getting results when you don't have any connections to the database or specific table defined? Besides that, I don't even see where you are executing your query.

Look into mysql_connect, mysql_select_db, and mysql_query.

DaWei · Feb 8th, 2006, 6:58 AM

Indeed. I presumed those were missing from the presentation, not the code. Lol.

k4pil · Feb 8th, 2006, 9:54 AM

Ok, sorry, i assumed that it was obvious, my fault.

Rest of code looks something like this;

(Toggle Plain Text)

session_start(); 
header("Cache-control: private"); //IE 6 Fix 
error_reporting(E_ALL);

if(isset($_SESSION['access']))
{
include '../connect.php';
include ('../template.php');

Above im connecting to the database.

(Toggle Plain Text)

 
$ID=$_POST['prodID']; 
$prodName=$_POST['prodName']; 
$prodDesc=$_POST['prodDesc']; 
$supplier=$_POST['supplier']; 
$cost=$_POST['cost'];

$trimmedCost = trim($cost);
$trimmedProdName = trim($prodName);
?>

Here i recieve the data. I have tried trim() function as i thought may be because of empty whitespaces.

(Toggle Plain Text)

 
<?include ('inventoryMenu.php');?>
<P>
you want to search; <br> 
<?echo "id: $ID"; ?>
<br>
<?echo "name: $prodName"; ?>
<br>
<?echo "description: $prodDesc"; ?>
<br>
<?echo "supplier: $supplier"; ?>
<br>
<?echo "cost: $cost"; ?>
<br>

Above i just check what has been passed and its working fine.

(Toggle Plain Text)

 
<?
$query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$trimmedProdName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$trimmedCost'";
$result = mysql_query($query); 
$num = mysql_numrows($result);
?>

Your search has resulted in the following;

(Toggle Plain Text)

 
<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Product ID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Product Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Product Description</font></th>
<th><font face="Arial, Helvetica, sans-serif">Supplier</font></th>
<th><font face="Arial, Helvetica, sans-serif">Cost</font></th>
</tr>


<?
$i=0;
while ($i < $num) {

$prodID=mysql_result($result,$i,"productID");
$prodName=mysql_result($result,$i,"prodName");
$prodDesc=mysql_result($result,$i,"prodDesc");
$supplier=mysql_result($result,$i,"supplier");
$cost=mysql_result($result,$i,"cost");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td>
</tr>

<?
$i++;
}


<?
}
else
{
$URL="http://www.lingan.co.uk";
header ("Location: $URL");
}
?>

Thats most of the code from my page, everything else is basic html formatting.

A bit long winded but hopefully someone can help.

I recall the use of % to trim characters being searched from a database and have a feeling it may be something like that which is missing, but obviously i am not sure.

Thanks in advance once again

Infinite Recursion · Feb 8th, 2006, 10:39 AM

If you are still getting all of the entries in the table, try echoing your sql string and running it manually in the mysql console to see what the results are. I would suggest generating the sql string dynamically from what the user requests and doing away with the empty fields being part of your query.

Just some basic db code to test your sql cmd. I haven't ran it as I do not have your db/table structure, but this is how I pull data from mysql.

[PHP]
// open connection to database
$connection = mysql_connect(HOST,USERID,PWD) or die ("Unable to connect!");
mysql_select_db("DBNAME") or die ("Unable to select database!");

// formulate and execute query
$query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);

echo "Results: $num_results";

for ($i=0; $i < $num_results; $i++)
{
$row = mysql_fetch_array($result);
echo $row["ProductID"];
echo " "
echo $row["prodName"];
echo " "
echo $row["prodDesc"];
echo " "
echo $row["supplier"];
echo " "
echo $row["cost"];
echo ""
}

// close connection
mysql_close($connection);
[/PHP]

DaWei · Feb 8th, 2006, 10:54 AM

My technique is very similar to IR's. I often use a custom error handler in more critical applications, so I know what's going on. Example:

(Toggle Plain Text)

$queryProducts = "SELECT ID, Item, Description, Color, Size, Price, OnHand, OfferQty 
		  FROM products
		  WHERE (OnHand >= OfferQty AND OnHand > 0 AND OfferQty <= 1000)
		  OR (OfferQty = 0)
		  ORDER BY Item";
$catalog = mysql_query ($queryProducts) or 
			dbError (mysql_error(), "product query", $queryProducts, true);

I also usually forego the counting of the number of rows returned by merely fetching rows until no more are available:

(Toggle Plain Text)

while ($rowData = mysql_fetch_array ($catalog))
{
   $items ['ID'] = $rowData ['ID']);
   $items ['Item'] = $rowData ['Item']);
   $items ['Description'] = $rowData ['Description']);
   etc.
   etc.
}

Feb 7th, 2006, 5:39 PM	#1
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	output from database Need some help with outputting from database. The code im using is; (Toggle Plain Text) query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'"; query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'"; The output is ; (Toggle Plain Text) <? $i=0; while ($i < $num) { $prodID=mysql_result($result,$i,"productID"); $prodName=mysql_result($result,$i,"prodName"); $prodDesc=mysql_result($result,$i,"prodDesc"); $supplier=mysql_result($result,$i,"supplier"); $cost=mysql_result($result,$i,"cost"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td> </tr> <? $i++; } <? $i=0; while ($i < $num) { $prodID=mysql_result($result,$i,"productID"); $prodName=mysql_result($result,$i,"prodName"); $prodDesc=mysql_result($result,$i,"prodDesc"); $supplier=mysql_result($result,$i,"supplier"); $cost=mysql_result($result,$i,"cost"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td> </tr> <? $i++; } However, the output is not the data being searched. Everything from the database seems to output from after the data being searched. Hope this makes sense. Any help Thanks in advance

Feb 7th, 2006, 6:23 PM	#2
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	output from database Need some help with outputting from database. The code im using is; (Toggle Plain Text) query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'"; query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'"; The output is ; (Toggle Plain Text) <? $i=0; while ($i < $num) { $prodID=mysql_result($result,$i,"productID"); $prodName=mysql_result($result,$i,"prodName"); $prodDesc=mysql_result($result,$i,"prodDesc"); $supplier=mysql_result($result,$i,"supplier"); $cost=mysql_result($result,$i,"cost"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td> </tr> <? $i++; } <? $i=0; while ($i < $num) { $prodID=mysql_result($result,$i,"productID"); $prodName=mysql_result($result,$i,"prodName"); $prodDesc=mysql_result($result,$i,"prodDesc"); $supplier=mysql_result($result,$i,"supplier"); $cost=mysql_result($result,$i,"cost"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td> </tr> <? $i++; } However, the output is not the data being searched. Everything from the database seems to output from after the data being searched. I tried (Toggle Plain Text) $trimmedProdName = trim($prodName); $trimmedProdName = trim($prodName); because i thought maybe it was the entries with empty data fields...but it dosn't help. Hope this makes sense. Any help Thanks in advance

Feb 7th, 2006, 6:47 PM	#3
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	You're really a little short of information. Without knowing what your database contents are like and exactly what you are expecting, one can't even tell if your query should be ORs or ANDs. There's no indication of where $num comes from. Stuff like that. Personally, I'd probably fetch an entire row into an array and build the table with that, but I guess that's just me. If you aren't sure what's coming back as the result, you might sprinkle a few echoes or print_rs around. You don't show the actual query, so we can't tell if you're prepared to deal with failure or if you're prepared to display any error messages or warnings. You might try running the query by hand directly with MySQL and seeing what it spits back at you. __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Feb 7th, 2006, 9:20 PM	#4
Infinite Recursion Programming Guru Join Date: Jul 2004 Location: United States Posts: 3,467 Rep Power: 8	How are you getting results when you don't have any connections to the database or specific table defined? Besides that, I don't even see where you are executing your query. Look into mysql_connect, mysql_select_db, and mysql_query. __________________ http://jasonpowers.net "There are a thousand hacking at the branches of evil to one who is striking at the root."

Feb 8th, 2006, 6:58 AM	#5
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	Indeed. I presumed those were missing from the presentation, not the code. Lol. __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Feb 8th, 2006, 9:54 AM	#6
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	Ok, sorry, i assumed that it was obvious, my fault. Rest of code looks something like this; (Toggle Plain Text) session_start(); header("Cache-control: private"); //IE 6 Fix error_reporting(E_ALL); if(isset($_SESSION['access'])) { include '../connect.php'; include ('../template.php'); session_start(); header("Cache-control: private"); //IE 6 Fix error_reporting(E_ALL); if(isset($_SESSION['access'])) { include '../connect.php'; include ('../template.php'); Above im connecting to the database. (Toggle Plain Text) $ID=$_POST['prodID']; $prodName=$_POST['prodName']; $prodDesc=$_POST['prodDesc']; $supplier=$_POST['supplier']; $cost=$_POST['cost']; $trimmedCost = trim($cost); $trimmedProdName = trim($prodName); ?> $ID=$_POST['prodID']; $prodName=$_POST['prodName']; $prodDesc=$_POST['prodDesc']; $supplier=$_POST['supplier']; $cost=$_POST['cost']; $trimmedCost = trim($cost); $trimmedProdName = trim($prodName); ?> Here i recieve the data. I have tried trim() function as i thought may be because of empty whitespaces. (Toggle Plain Text) <?include ('inventoryMenu.php');?> <P> you want to search; <br> <?echo "id: $ID"; ?> <br> <?echo "name: $prodName"; ?> <br> <?echo "description: $prodDesc"; ?> <br> <?echo "supplier: $supplier"; ?> <br> <?echo "cost: $cost"; ?> <br> <?include ('inventoryMenu.php');?> <P> you want to search; <br> <?echo "id: $ID"; ?> <br> <?echo "name: $prodName"; ?> <br> <?echo "description: $prodDesc"; ?> <br> <?echo "supplier: $supplier"; ?> <br> <?echo "cost: $cost"; ?> <br> Above i just check what has been passed and its working fine. (Toggle Plain Text) <? $query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$trimmedProdName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$trimmedCost'"; $result = mysql_query($query); $num = mysql_numrows($result); ?> <? $query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$trimmedProdName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$trimmedCost'"; $result = mysql_query($query); $num = mysql_numrows($result); ?> <p> Your search has resulted in the following; (Toggle Plain Text) <table border="1" cellspacing="2" cellpadding="2"> <tr> <th><font face="Arial, Helvetica, sans-serif">Product ID</font></th> <th><font face="Arial, Helvetica, sans-serif">Product Name</font></th> <th><font face="Arial, Helvetica, sans-serif">Product Description</font></th> <th><font face="Arial, Helvetica, sans-serif">Supplier</font></th> <th><font face="Arial, Helvetica, sans-serif">Cost</font></th> </tr> <? $i=0; while ($i < $num) { $prodID=mysql_result($result,$i,"productID"); $prodName=mysql_result($result,$i,"prodName"); $prodDesc=mysql_result($result,$i,"prodDesc"); $supplier=mysql_result($result,$i,"supplier"); $cost=mysql_result($result,$i,"cost"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td> </tr> <? $i++; } <? } else { $URL="http://www.lingan.co.uk"; header ("Location: $URL"); } ?> <table border="1" cellspacing="2" cellpadding="2"> <tr> <th><font face="Arial, Helvetica, sans-serif">Product ID</font></th> <th><font face="Arial, Helvetica, sans-serif">Product Name</font></th> <th><font face="Arial, Helvetica, sans-serif">Product Description</font></th> <th><font face="Arial, Helvetica, sans-serif">Supplier</font></th> <th><font face="Arial, Helvetica, sans-serif">Cost</font></th> </tr> <? $i=0; while ($i < $num) { $prodID=mysql_result($result,$i,"productID"); $prodName=mysql_result($result,$i,"prodName"); $prodDesc=mysql_result($result,$i,"prodDesc"); $supplier=mysql_result($result,$i,"supplier"); $cost=mysql_result($result,$i,"cost"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo "$prodID"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodName; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $prodDesc; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $supplier; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $cost; ?></font></td> </tr> <? $i++; } <? } else { $URL="http://www.lingan.co.uk"; header ("Location: $URL"); } ?> Thats most of the code from my page, everything else is basic html formatting. A bit long winded but hopefully someone can help. I recall the use of % to trim characters being searched from a database and have a feeling it may be something like that which is missing, but obviously i am not sure. Thanks in advance once again

Feb 8th, 2006, 10:39 AM	#7
Infinite Recursion Programming Guru Join Date: Jul 2004 Location: United States Posts: 3,467 Rep Power: 8	If you are still getting all of the entries in the table, try echoing your sql string and running it manually in the mysql console to see what the results are. I would suggest generating the sql string dynamically from what the user requests and doing away with the empty fields being part of your query. Just some basic db code to test your sql cmd. I haven't ran it as I do not have your db/table structure, but this is how I pull data from mysql. [PHP] // open connection to database $connection = mysql_connect(HOST,USERID,PWD) or die ("Unable to connect!"); mysql_select_db("DBNAME") or die ("Unable to select database!"); // formulate and execute query $query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR cost = '$cost'"; $result = mysql_query($query); $num_results = mysql_num_rows($result); echo "Results: $num_results"; for ($i=0; $i < $num_results; $i++) { $row = mysql_fetch_array($result); echo $row["ProductID"]; echo "<br>" echo $row["prodName"]; echo "<br>" echo $row["prodDesc"]; echo "<br>" echo $row["supplier"]; echo "<br>" echo $row["cost"]; echo "<p>" } // close connection mysql_close($connection); [/PHP] __________________ http://jasonpowers.net "There are a thousand hacking at the branches of evil to one who is striking at the root."

Feb 8th, 2006, 10:54 AM	#8
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	My technique is very similar to IR's. I often use a custom error handler in more critical applications, so I know what's going on. Example: (Toggle Plain Text) $queryProducts = "SELECT ID, Item, Description, Color, Size, Price, OnHand, OfferQty FROM products WHERE (OnHand >= OfferQty AND OnHand > 0 AND OfferQty <= 1000) OR (OfferQty = 0) ORDER BY Item"; $catalog = mysql_query ($queryProducts) or dbError (mysql_error(), "product query", $queryProducts, true); $queryProducts = "SELECT ID, Item, Description, Color, Size, Price, OnHand, OfferQty FROM products WHERE (OnHand >= OfferQty AND OnHand > 0 AND OfferQty <= 1000) OR (OfferQty = 0) ORDER BY Item"; $catalog = mysql_query ($queryProducts) or dbError (mysql_error(), "product query", $queryProducts, true); I also usually forego the counting of the number of rows returned by merely fetching rows until no more are available: (Toggle Plain Text) while ($rowData = mysql_fetch_array ($catalog)) { $items ['ID'] = $rowData ['ID']); $items ['Item'] = $rowData ['Item']); $items ['Description'] = $rowData ['Description']); etc. etc. } while ($rowData = mysql_fetch_array ($catalog)) { $items ['ID'] = $rowData ['ID']); $items ['Item'] = $rowData ['Item']); $items ['Description'] = $rowData ['Description']); etc. etc. } __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Programming Forums > Web Development and Design > PHP

output from database