Select rows from database fields based on form submit array

General PHP Help
#1

I have a column named categories and a column named rates in my products table each category has a different rate associated with it so if a user inputs data like in the below example:

Example: user input form

Auto Parts in category
Air Conditions in category
Bicycles in category

I need to search the table products for the fields where the above inputs match and find the associated rate for each input

and echo the rate for each

Auto Parts 60%
Air Conditons 45%
Bicycles 25%

html form snippet

[php]<?php
//connect to mysql database
$connection = mysqli_connect(“localhost”,“root”,"",“customs”) or die("Error " . mysqli_error($connection));

   //fetch data from database
	$sql = "select categories from lt_products";
	$result = mysqli_query($connection, $sql) or die("Error " . mysqli_error($connection));

?>

<TD width="156"> <input name="pcategories[]" type="text" list="productcategories" class="input_field2" autocomplete="off" value="Description of Goods" id="pcategories" onfocus="clearText(this)" onblur="clearText(this)">
		<datalist id="productcategories">
    <?php while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){ ?>
            <option value="<?php echo $row['categories']; ?>"><?php echo $row['categories']; ?></option>
    	<?php } ?>
		</datalist>
<?php mysqli_close($connection); ?>

$pcategories=$_POST[‘pcategories’];[/php]

I’m stuck at this point

#2

For starters, you should be using the record id’s not the column data for your select value and in your query, but as is, this query should work. You will have to build the OR’s in your code from the POST array.

[php]SELECT categories,rate FROM products WHERE (categories= “Auto Parts” OR categories= “Air Conditions” OR categories= “Bicycles”) [/php]

#3

Thank you for this but the table has hundreds of different categories and the user gets a datalist to choose from in the input field so i was trying to some how match the users post to the fields in the table to get the rate for what they choose. i cant construct it this way, i dont think because, i woud have to do an or for hundreds of categories. I have been trying something like this but i can’t get it to work

$ipcategories = implode(’,’, $pcategories);
$query = "SELECT categories, rate, elevy FROM ".$db_prefix.“products WHERE categories IN ($ipcategories);”;

im stuck here

Also tried this and cant get it to work

<?php $con=mysqli_connect("localhost","root","","customs"); $query = mysqli_query($con, "select categories, rate from ".$db_prefix."products where categories IN(".implode(',',$pcategories). "'") or die("Error " . mysqli_error($con)); while($pc = mysqli_fetch_array($query, MYSQLI_ASSOC)) { echo $pc['categories']; echo $pc['rate']; } Any help would be really appreciated as i pretty much don't know where to go from here
#4

You need to create a dynamic query. I dont have time to get into right now. [member=62829]scottlpool2003[/member] posted a dynamic insert that will help you do a dynamic select. http://www.phphelp.com/forum/general-php-help/insert-(array-$values-$tablename/

This is an idea of what you need. You will have to work it out for your use:

[php]if(isset($_POST[‘send’])):
$sql = “SELECT project_id, phase
FROM csvtb”;

foreach ($_POST as $key => &$value):
    if(array_key_exists($key,$allowed) && !empty($value)):
        $search[] = $allowed[$key]['field'] . $allowed[$key]['comp'] . ':' . $key;
    endif;
endforeach;

if(!empty($search)):
    $sql .= " WHERE ";
    $sql .= implode(" AND ",$search);
endif;
//echo $sql; 

$query = $db->prepare($sql);

foreach ($_POST as $key => &$value) { 
    if(array_key_exists($key,$allowed) && !empty($value)):
        $query->bindParam(':' . $key, $value);
    endif;
}

$query->execute();       

$search_results .= '<table>'."\r";

while($row = $query->fetch(PDO::FETCH_ASSOC)){[/php]
#5

Thanks for putting me on this track but i am still not getting the desired results. I am actually getting the list of all the categories and all the rates in the table not actually the ones that the user post.

Here is the modifications i’ve made i know something is wrong just don’t know what, also need help with the where and implode statements.

results after running the code looks like this

Alarm Systems 45
Air Condition 40
Auto Parts 60
Baby Bottles Free
Bicycles 35
Televisions 25
//whole table results

But user may have only posted say
Air Condition
Televisions

[php]<?php
if(isset($_POST[‘pcategories’]));
$sql = “SELECT categories, rate
FROM lt_products”;

  foreach ($_POST as $key => &$value):
      if(array_key_exists($key,$pcategories) && !empty($value)):
          $search[] = $pcategories[$key]['categories'] . $pcategories[$key]['categories'] . ':' . $key;
      endif;
  endforeach;
 
 if(!empty($search)):
     $sql .= " WHERE ";
     $sql .= implode(" AND ",$search);
 endif;
 //echo $sql; 
 
 $db = new PDO('mysql:host=localhost;dbname=customs', 'root','' );
 

 $query = $db->prepare($sql);
 
 foreach ($_POST as $key => &$value) { 
     if(array_key_exists($key,$pcategories) && !empty($value)):
         $query->bindParam(':' . $key, $value);
     endif;
 }
 
 $query->execute();       
 
 
 
 while($row = $query->fetch(PDO::FETCH_ASSOC)){
 
 echo "<tr><td>" . $row["categories"]. "</td><td>" . $row["rate"]. " <br></td></tr>";
 }

?>
[/php]
[size=8pt]Please use coding tags when showing code for it makes it easier to read - Thank You [/size]

Thanks Once Again for your help.

#6

If you run this:
[php]SELECT categories,rate FROM products WHERE (categories= “Auto Parts” OR categories= “Air Conditions” OR categories= “Bicycles”)
[/php]

Does it return just the data from those three categories?

#7

Yes it works when i use the OR below is what i did to check it.

<?php $r = my_query("select id, categories, rate from " . $db_prefix . "products where categories='Bicycles' or categories='Televisions' or categories='Auto Parts' or categories='Alarm Systems' "); while($row = mysql_fetch_assoc($r)) { ?> 
					<?php echo $row["categories"]; ?> <?php echo $row["rate"]; ?>

<?php } ?>

out put was
Alarm Systems 45 Auto Parts 60 Bicycles 35 Televisions 25

#8

Yeah, this needs modification:

[php]if(!empty($search)):
$sql .= " WHERE “;
$sql .= implode(” OR ",$search); // Changed And to OR
endif;
[/php]

#9

Changed AND to OR still getting all the fields.

How should the where and the or statements be written or should i leave them just like that.

if(!empty($search)):
$sql .= " WHERE “;
$sql .= implode(” OR ",$search);
endif;

#10

I’m away from my computer to test it, but I am betting the if statement is the issue. I’ll let you know later.

#11

Okay, it isn’t the foreach loop itself. It is the query string. Did you echo out the sql you were passing?

Here is what I have with my test values:
[php]
foreach ($_POST as $key => $value):
if(array_key_exists($key,$pcategories) && !empty($value)):
$search[] = “categories = :$key”;
endif;
endforeach;

if(!empty($search)):
$sql .= " WHERE “;
$sql .= implode(” OR ",$search);
endif;

echo “

”;

echo $sql;

echo “
”;[/php]

The output for the WHERE clause is this,

SELECT categories, rate FROM lt_products WHERE categories = :aa OR categories = :ac OR categories = :ae

Those were my sample post values, which is correct. Question is, does yours match it?

#12

Pretty sure the WHERE conditions need to be in (). You had it in a previous post.

WHERE (categories = :aa OR categories = :ac OR categories = :ae)

#13

You could also implode the placeholders and use IN( :val1, :val2, :val3, :val4 ).

Do you have a version where it says it’s required?

While the parentheses makes grouping easier, I don’t believe I have seen a requirement in any RDBMS outside of function calls.

#14

Do some testing. The parenthesis makes a difference with the OR conditions. You can get two different results, one of which will be wrong. Has to do with operator precedence.

#15

Sort of supporting Kevins argument.

They are not required in this case, as a single conditional is used. When using different type, they are.

#16

Looking into it. May just be when you have AND with OR’s. AND has higher precedence.

*EDIT: Yes, it makes a difference when you have AND as well as OR’s.

#17

Hey Guys Thanks for the help. I ended up getting this code below to work, but I now have a new problem, any help would be greatly appreciated.

<?php $sql = "SELECT `categories`, `rate` FROM `lt_products` WHERE `categories` IN ('".implode("','",$pcategories)."')"; $result = mysql_query($sql)or die(mysql_error()); while($row = mysql_fetch_assoc($result)) {?> 
		<?php echo $row["rate"]; ?> <br>
<?php } ?>

when the user inputs the form, I am using a foreach loop to get the categories that they chose example:

<?php foreach($pcategories as $value){echo "$value
";}?>

The rate is then being selected from the query above

$qty $pcategories $cost $Rate
1 Bicycles 100 45
7 Auto Parts 200 60
5 Alarm Sys 300 35

Here’s the problem the code is pulling the correct rates base on the categories the user in putted
but the rates are not coming out to match the order of the user input Example

Above rates should actually look like this below but instead that is what im getting above

$qty $pcategories $cost $rate
1 Bicycles 100 35
7 Auto Parts 200 60
5 Alarm Sys 300 45

The select query seems to be coming out in alphabetical order based on the $pcategories field in table.

I dont know whats the best way to fix this so that the actual rate matches the categories the user chose.

#18

Before you go further, fix the deprecated code. mysql_ database functions were removed from PHP for a reason, don’t use them just because you can in older versions.

#19

Forgive me! I revised it let me know if this is OK.

<?php $connection = mysqli_connect("localhost","root","","customs") or die("Error " . mysqli_error($connection)); $sql = "SELECT `categories`, `rate`, `elevy` FROM `lt_products` WHERE `categories` IN ('".implode("','",$pcategories)."')"; $result = mysqli_query($connection, $sql)or die(mysql_error()); while($row = mysqli_fetch_assoc($result)) {?> 
						  <?php echo $row["rate"]; ?> <br>
	  <?php } ?>
#20

Is the idea that the rates will populate next to the already typed categories? Why not add the rates and categories at the same time, that way they match?

Sponsor our Newsletter | Privacy Policy | Terms of Service