Retrieving Data

[LWolf]

I have a basic table where I am trying to retrieve records that are filtered by a form request. Here is the code I have...

$ps = $pdo->prepare("SELECT * FROM `Products` Where `Vendor` LIKE '%?%'");
$ps->execute(array($_POST['Vendor']));;
echo "post=" . $_POST['vendor']; ////Displays correct data from form request
$count = $ps->rowCount();
echo "Count=" . $count; ////Count = 0 although I know for a fact that there is 1 record that should be in there

////Used for display of records
foreach ($ps as $row){
echo $row, PHP_EOL . "xxx<br>";
}

Where is the incorrect code? I am new to php and pdo. Thank you in advance

K

 

[jpadie]

fwiw the reason why i used the products table was

1. i didn't know anything about the vendor table
2. there was no point in populating a select from vendors that don't have a corresponding entry in the products table.

 

[LWolf]

So the test page works great. FYI...all vendors will have products but it is good coding to run the distinct selection anyway. I took the section of code that prints the recordsets & inserted it into the main page & there is no display of the records. The $_POST['vendor'] is displaying. This is so bizarre, same database-same code.

Here is the form and display....
<form name=productselection action="" method="POST">
<input type="hidden" name="search" value="y">
<div id=ptype>
<p>
<b>Select product type:</b> &nbsp;
<input type=checkbox name=alltypes onclick="ap(1);">All Products
<input type=checkbox name=organic onclick="ap(2);">Organic
<input type=checkbox name=glutenfree onclick="ap(3);">Gluten Free
<input type=checkbox name=natural onclick="ap(4);">All Natural
<input type=checkbox name=nongmo onclick="ap(5);">Non-GMO <br>
</p>

<p>
<?php
$ps = $pdo->prepare("SELECT `Vendors` FROM `Vendors` Order By 'Vendors'");
$ps->execute();
$ps->setFetchMode(PDO::FETCH_COLUMN, 0);
?>
<b>Select Vendor: &nbsp;
<select name="vendor">
<option value="ALLVENDORS">All Vendors</option>
<?php
foreach ($ps as $row){
$var=str_replace("'","\'",$row);
echo '<option value="' . $row, PHP_EOL . '">' . $row, PHP_EOL . "</option>";
}
?>
</select> &nbsp;

<?php
$ps = $pdo->prepare("SELECT `Category` FROM `Category` Order By 'Category'");
$ps->execute();
$ps->setFetchMode(PDO::FETCH_COLUMN, 0);
?>
<b>Select Department:</b>
<select name=department>
<option value="ALLDepartments">All Departments</option>
<?php
foreach ($ps as $row){
echo "<option value='" . $row, PHP_EOL . "'>" . $row, PHP_EOL . "</option>";
}
?>
</select>
</p>
<input type="submit" value="Search">
</div>
</form>

Here is the display copied from the test page you provided (again which worked displaying record data)...

echo "POST=" . $_POST['vendor'] . "<br>";
if(isset($_POST) && isset($_POST['vendor']) && !empty($_POST['vendor'])):
$sql = "SELECT * FROM Products WHERE Vendor LIKE ?";
$ps = $pdo->prepare($sql);
if($ps == false):
print_r($pdo->errorInfo());
die;
endif;
$result = $ps->execute(array( '%' . $_POST['vendor'] . '%' ));
if($result == false):
print_r($ps->errorInfo());
die;
endif;
while($row = $ps->fetchObject()):
$rows[] = $row;
endwhile;
print_r($rows);
endif;
echo "POST=" . $_POST['vendor'] . "<br>";

I am sure this is a simple fix. Thank you again.

K

 

[jpadie]

try this code instead.
(and please always try to post code within [ignore]

[/ignore] tags to aid readability

<form name=productselection action="" method="POST">
<input type="hidden" name="search" value="y">
<div id=ptype>
<p>
<b>Select product type:</b> &nbsp;
<input type=checkbox name=alltypes onclick="ap(1);">All Products
<input type=checkbox name=organic onclick="ap(2);">Organic
<input type=checkbox name=glutenfree onclick="ap(3);">Gluten Free
<input type=checkbox name=natural onclick="ap(4);">All Natural
<input type=checkbox name=nongmo onclick="ap(5);">Non-GMO <br>
</p>

<p>
<?php
$ps = $pdo->prepare("SELECT `Vendors` FROM `Vendors` Order By Vendors");
$ps->execute();
//$ps->setFetchMode(PDO::FETCH_COLUMN, 0);
?>
<b>Select Vendor: &nbsp;
<select name="vendor">
<option value="ALLVENDORS">All Vendors</option>
<?php
$format = '<option value="%s">%s</option>' . "\n";
while ($row = $ps->fetchObject()):
	printf($format, $row->Vendors, htmlspecialchars($row->Vendors));
endwhile;
?>
</select> &nbsp;

<?php
$ps = $pdo->prepare("SELECT `Category` FROM `Category` Order By 'Category'");
$ps->execute();
?>
<b>Select Department:</b>
<select name="department">
<option value="ALLDepartments">All Departments</option>
<?php
while($row = $ps->fetchObject() ):
	printf($format, $row->Category, htmlspecialchars($row->Category));
endwhile;
?>
</select>
</p>
<input type="submit" value="Search">
</div>
</form>
<?php

echo "POST=" . $_POST['vendor'] . "<br>";
if(isset($_POST) && isset($_POST['vendor']) && !empty($_POST['vendor'])):
	$sql = "SELECT * FROM Products WHERE Vendor LIKE ?";
	$ps = $pdo->prepare($sql);
	if($ps == false):
		print_r($pdo->errorInfo());
		die;
	endif;
	if($_POST['vendor'] == "ALLVENDORS"):
		$result = $ps->execute(array( '%' ));
	else:
		$result = $ps->execute(array( '%' . $_POST['vendor'] . '%' ));
	endif;
	
	if($result == false):
		print_r($ps->errorInfo());
		die;
	endif;
	$rows = array();
	while($row = $ps->fetchObject()):
		$rows[] = $row;
	endwhile;
	print_r($rows);
endif;
echo "POST=" . $_POST['vendor'] . "<br>";

 

[LWolf]

It worked great. So now I need paging so I used this code to try & get a record count but it throws the following error...

$count = $rows->fetch(PDO::FETCH_NUM);

Fatal error: Call to a member function fetch() on a non-object in .../index.php on line 229

I found 3 different variations on the count code but they all throw the same error. Is there a tutorial you would recommend for paging also?

 

[jpadie]

that doesn't fetch the number of returned rows.

there is a paging example in the FAQ to this forum. You will have to adapt it form mysql_* to the pdo format. which is trivial.

with mysql you have an opportunity to return the number of found rows as well as the limited recordset from a given offset. that's probably the neatest. search for SQL_CALC_FOUNDROWS. the other alternative is to run two queries - one as a COUNT(*) and the other as a recordset retrieval. the former is faster.

 

[LWolf]

I'm chugging along and somewhat catching on. I have my loops all set and studying paging. This should be the last question before finishing with the pagin...

I am trying to use data fields in the recordset and thought $rows['Product'] would work but it does not. I am actually using the (modified) while loop posted above, what am I doing wrong? Is this not the correct pdo syntax?

 

[jpadie]

in the last of my substantive posts the $rows array is a numeric array of row objects. so you would loop like this

foreach($rows as $row):
 echo $row->Product;
 //etc
endforeach;

 

[LWolf]

I have the basis for the page and am now adjusting to incorporate usefulness. The "vendor" sql is actually going to be used with a string predetermined for the parameters. However, when I tried to do this it 1. did not work with the '?' and 2. does not select the desired results. Here is the logic...

-to find single vendor or all vendors...

$srch="";
if (isset($_POST['vendor']) && $_POST['vendor']!="All Vendors"){$srch="Vendor LIKE " . $_POST['vendor'];}
elseif (isset($_POST['vendor']) && $_POST['vendor']=="All Vendors"){$srch="Vendor LIKE " . $_POST['vendor'];}

-to find department...

if ($_POST['department']!="All Departments" && $srch==""){$srch="Department LIKE " . $_POST['department'];}
else {$srch = $srch . " and Department LIKE " . $_POST['department'];}

These results are giving me this when I select a vendor (with an apostrophe) and no department...
Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''s and Department LIKE ALL Departments Order By Product' at line 1 )

No apostrophe...
Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALL Departments Order By Product' at line 1 )

And with the ?...
=SELECT * FROM Products WHERE Vendor LIKE ? and Department LIKE ? Order By Product (this is the ehco for the sql)
Array ( [0] => HY093 [1] => [2] => )

None of this produces any results.

What am I doing wrong?

 

[jpadie]

you must enquote string variables that are used in a query.
and you must escape all variables over which you cannot be certain.

if (isset($_POST['vendor']) && $_POST['vendor']!="All Vendors"){
  $srch = sprintf(  ' Vendor LIKE "%%%s%%" ', 
                    mysql_real_escape_string(trim($_POST['vendor'])
                );
}elseif( isset($_POST['vendor']) && $_POST['vendor']=="All Vendors"){
  $srch = " 1=1 "; // if you want all vendors then you need to provide a dummy comparison.
}

of if you are using PDO

$query = "
SELECT    * 
FROM      Products 
WHERE     Vendor LIKE :vendor AND Department LIKE :department 
ORDER BY  Product
"; 
$srch = array();
foreach(array('vendor','department') as $v):
 if(isset($_POST[$v]) && substr($_POST[$v],0,3) !== 'All') ):
  $params[$v] = '%' . $_POST[$v] . '%';
 else:
  $params[$v] = '%';
 endif;
endforeach;
$s = $pdo->prepare($query);
if($s === false):
 print_r($pdo->errorInfo());
 die;
endif;
$result = $s->execute($params);
if($result === false):
 print_r($s->errorInfo());
 die;
endif;
while($row = $s->fetchObject()):
 echo '<pre>' . print_r($row, true) . '</pre>';
endwhile;