How To Convert Explode Result Into Php Variables That Can Be Used In Mysql Query

[jkshaver]

I am passing user inputs from a page called add_product.php. There is a drop down menu that CONCATs two table fields to form one name ($category). After passing $category to the product_created.php page, I can break this back down into the two respective fields using explode() on but how do I create variable out of the results?

 

Here is the snippet from add_product.php

 

    <?php
 //Grab the Product Category name and Bucket name from productcategory and bucket tables in DB
 $qry=mysql_query("SELECT CONCAT(bucket.b_name, ' : ', productcategory.name) AS category
    FROM productcategory, bucket, product
    WHERE product.category_id = productcategory.id 
    AND productcategory.bucket_id = bucket.id", $con);
 if(!$qry)
 {
 die("Query Failed: ". mysql_error());
 }
 ?>

       <p>Category: 
       <select name="category" id="category">
 <?php
 //take the responses from the query above and show then in a drop down menu
       while($row=mysql_fetch_array($qry))
       {
  echo $category = "<option value='".$row['category']."'>".$row['category']."</option>";

 }
       ?>
       </select>

 

I then pass that to product_created.php and this is what I have so far and where I am stuck:

 

//This gets all the other information from the form

$category=$_POST['category'];



// Explode category variable to get bucket.b_name and productcategory.name
list($b_name, $pc_name) = explode(":", $category);
echo $b_name.'<br/>'; // bucket.b_name
echo $pc_name.'<br/>'; // productcategory.name
//the above code works and prints the separate category names onscreen


$categories = array($b_name,$pc_name);
print "The product category name is $pc_name";
//test two: this code works and prints just the productcategory name


//Define the query to grab the product category id where pc_name equals the product category name
$sql = "SELECT id FROM productcategory WHERE name = '$pc_name'";


//submit the query and capture the result
$result = mysql_query($sql) or die(mysql_error());
echo $result;


//find out how many rows were retrieved
$numRows = mysql_num_rows($result);

//current result is 0 and should be 1 if successful

//this is my attempt to convert the productcategory id into a variable that can be passed back into the DB
$sql_q = "SELECT id FROM productcategory WHERE name = '$pc_name'" or die(mysql_error());
$sql = mysql_fetch_array($sql_q);
//above sets the category_id array
//example  usage: $sql['mysql_col_name']
echo $sql['category_id'];
//the above prints the category_id
foreach ($sql as $category_id) {
//echo $category_id.'<br/>';
}

//Define query to add product to DB
$qry=mysql_query("INSERT INTO product(name,category_id,slug,old_price,price,description,video_url,status,date_posted)VALUES('$name','$category_id','$slug','$o_price','$price','$desc','$video','$status','$date')", $con);
if(!$qry)
{
die("Query Failed: ". mysql_error());
}
else
{
echo "<br/>";
echo "Product Added Successfully";
echo "<br/>";
}

 

I know some of this is redundant but I am trying to teach myself how to solve this and this was the best way for me to learn.

 

I feel like I am really close, but that I have gone round and round at this point and have just confused myself. I understand that there may be other issues with my code as it is written, but I really need to focus on resolving this first. Any help, advise, teaching, would be most appreciated.

[Manixat]

just to make sure I understood correctly - you break down a string using explode and want to use the results in a new query - is that correct ?

[Scott_S]

One of the easiest ways to "To Convert Explode Result Into Php Variables" is to use list().

[jkshaver]

just to make sure I understood correctly - you break down a string using explode and want to use the results in a new query - is that correct ?

 

Yes, this is what I am trying to do. Of course, if I have made this more difficult than it needs to be, please give me your advice. Otherwise, any ideas?

 

Scott_S - I did try list and it will echo the variables. But when I go to use them in the sql query, it returns no results. When I replace the variable in the query statement with the actual content (ex: "Augustine"), I do return a value so I am not sure why it is not working.

 

Any/all help is appreaciated. Thank you for taking time to look at this.

Edited November 14, 2012 by jkshaver

[Manixat]

Well explode breaks down your string into an array which you can still use as individual variables ? Another option is implode() I guess. I don't really see where the problem is ?

Edited November 14, 2012 by Manixat

[Scott_S]

If I understand this properly, you are having trouble here:

 

//Define the query to grab the product category id where pc_name equals the product category name
$sql = "SELECT id FROM productcategory WHERE name = '$pc_name'";

//submit the query and capture the result
$result = mysql_query($sql) or die(mysql_error());
echo $result;

 

You can use the sting literal "Augustine" and everything works. If that is the case try changing

 

$sql = "SELECT id FROM productcategory WHERE name = '$pc_name'";

 

to

 

$sql = "SELECT id FROM productcategory WHERE name = " . $pc_name;

[jkshaver]

If I understand this properly, you are having trouble here:

//Define the query to grab the product category id where pc_name equals the product category name$sql = "SELECT id FROM productcategory WHERE name = '$pc_name'";//submit the query and capture the result$result = mysql_query($sql) or die(mysql_error());echo $result;

You can use the sting literal "Augustine" and everything works. If that is the case try changing

$sql = "SELECT id FROM productcategory WHERE name = '$pc_name'";

to

$sql = "SELECT id FROM productcategory WHERE name = " . $pc_name;

Thank you Scott. I changed my query to reflect your recommendation.

$sql = "SELECT id FROM productcategory WHERE name = ". $pc_name;

I get the below output:

Accessories

Argentine strings

The product category name is Argentine strings

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'strings' at line 1

 

I tried backticks around the column names, but get the same error. Any other ideas?

Edited November 15, 2012 by jkshaver

[Scott_S]

Alright that is closer. How about:

 

$sql = "SELECT id FROM productcategory WHERE name = '". $pc_name . "'";

[Barand]

 

You can use the sting literal "Augustine" and everything works. If that is the case try changing

 

$sql = "SELECT id FROM productcategory WHERE name = '$pc_name'";

 

to

 

$sql = "SELECT id FROM productcategory WHERE name = " . $pc_name;

 

Your revised suggestion, if $pc_name contains 'Augustine', will result in an unknown column name error (unless the OP happens to have a column named Augustine in the table. The first syntax was correct.

 

 

 

Alright that is closer. How about:

 

$sql = "SELECT id FROM productcategory WHERE name = '". $pc_name . "'";

 

Which is what the OP had originally, except your concatenation makes it more difficult to read.

 

Why are you leading him round in circles?

[jkshaver]

New error:

 

Accessories

Argentine strings

The product category name is Argentine strings

Resource id #2

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/waloraweb009/b2529/moo.jkshaver/devarea/caravan/admin/product_created.php on line 76

 

Warning: Invalid argument supplied for foreach() in /hermes/waloraweb009/b2529/moo.jkshaver/devarea/caravan/admin/product_created.php on line 81

A total of 0 records were added.

 

Resource id#2 error as a result of

$sql = "SELECT id FROM productcategory WHERE name = '". $pc_name . "'";


//submit the query and capture the result
$result = mysql_query($sql) or die(mysql_error());
echo $result;

 

and the other errors stem from this query using foreach:

$sql_q = "SELECT id FROM productcategory WHERE name = '". $pc_name . "'" or die(mysql_error());
$sql = mysql_fetch_array($sql_q);
//above sets the category id array
//example usage: $sql['mysql_col_name']
echo $sql['category_id'];
//the above prints the category_id
foreach ($sql as $category_id) {
//echo $category_id.'<br/>';
}

 

Not sure if that made it better or worse.

Edited November 15, 2012 by jkshaver

[Barand]

Before you can fetch a row you have to call mysql_query(). All you have done is define a string.

 

As you will only have one field in the row (id) then your use of foreach is pointless.

[Barand]

try

<?php
   $sql_q = "SELECT id FROM productcategory WHERE name = '$pc_name'";
   $res = mysql_query($sql_q) or die(mysql_error());
   $sql = mysql_fetch_assoc($res);
   //above sets the category id array
   //example usage: $sql['mysql_col_name']
   echo $sql['id'];

?>

[jkshaver]

try

<?php
$sql_q = "SELECT id FROM productcategory WHERE name = '$pc_name'";
$res = mysql_query($sql_q) or die(mysql_error());
$sql = mysql_fetch_assoc($res);
//above sets the category id array
//example usage: $sql['mysql_col_name']
echo $sql['id'];

?>

 

Yes, that works and outputs the 'id'. But now how do I take $sql['id'] and use it in my INSERT INTO query as a VALUE? Is there a way to do something similar to $sql['id'] AS $category_id?

 

Below is my INSERT INTO query statement:

$qry=mysql_query("INSERT INTO product(name,category_id,slug,old_price,price,description,video_url,status,date_posted)VALUES('$name','$category_id','$slug','$o_price','$price','$desc','$video','$status','$date')", $con);
if(!$qry)
{
die("Query Failed: ". mysql_error());
}
else
{
echo "<br/>";
echo "Product Added Successfully";
echo "<br/>";
}

 

You help is very much appreciated. You have helped me learn something new today!

[Barand]

$category_id = $sql['id'];

// then your query as it is
$qry=mysql_query("INSERT INTO product(name,category_id,slug,old_price,price,description,video_url,status,date_posted)VALUES('$name',$category_id,'$slug','$o_price','$price','$desc','$video','$status','$date')", $con);

 

Or you can just replace $category_id in your query

 

$qry=mysql_query("INSERT INTO product(name,category_id,slug,old_price,price,description,video_url,status,date_posted)VALUES('$name',{$sql['id']},'$slug','$o_price','$price','$desc','$video','$status','$date')", $con)

 

NOTE: Numeric values in queries should not be in quotes.

Edited November 15, 2012 by Barand

[jkshaver]

oh my gosh! I feel like such a dunce. Thank you for helping me with this. I think I had gone round and round trying to figure this out on my own that I over-complicated things. Sometimes I just need to go back to the basics. I really do appreciate your help with this. This is working now!