Accessing a field that contains multiple values

[kellyalw]

I'm stumped.

Here's what I need to happen:

I have several pages containing fifteen thumbnail pics each. Clicking on a thumbnail takes a customer to a full page describing the item, and has buttons for adding to cart & viewing cart contents.

If an item sells, the shopping cart service (Mal's) sends a remote call, that populates my db (that part works). That's the only function for the table.

SO, I need to write a script that will intervene when the thumbnail pic is clicked. The script will query the database to see if any number contained in the field “cart” (always four alphanumerics - x + three numbers [e.g. x123, x004, x999]) = the item number of the piece the person is trying to view.

If they’re equal, the browser is either redirected to a canned “sold” page – or (better still) the thumbnail pic changes to a pic that reads “sold”.

If ? then the browser proceeds on to the item description page.

When I have an opportunity, I then go in and manually clear the db row. Once I’ve replaced a sold item with another.

The main problem is that I'm locked into populating my db table from a remote call that I can't configure.
Only the fields “cart” contains multiple values. The values in the field "cart" are delimited by colons. If one person purchases two or more items, each item is further delimited by a tilde. The values correspond with “description, quantity, price, shipping units, stock code”

So the “cart” field for two items sold could look like:

description1 : 1 : 15.50 : 1 : x003~ description2 : 1 : 12.95 : 1 : x136

all I need are the values “x###“ for each “cart” field in every row.

just in case it’s relevant, here’s the working script that populates the db.

<?PHP
include("config.php");
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database $DBName");

mysql_select_db($DBName) or die("Unable to select database $DBName");

$order_date=time("m j y h:i");

mysql_query("REPLACE INTO orders (username,id,ip,date,method,cart,discount,subtotal,shipping,tax,total,inv_name,inv_company,inv_addr1,inv_addr2,inv_state,inv_zip,inv_country,tel,email,message)

VALUES ('$username',$id,'$ip','$order_date','$method','$cart','$discount','$subtotal','$shipping','$tax','$total','$inv_name','$inv_company','$inv_addr1','$inv_addr2','$inv_state','$inv_zip','$inv_country','$tel','$email','$message')");
?>

Any ideas at all on how to get this all to come together?
Am I going about this all wrong?

[TheDefender]

Two ways...

Either [man]substr()[/man]
substr($cart, -4);

or

[man]explode()[/man]
explode(":", $cart);

The first one will only return the last 4 characters of the field. Assuming it will always be xnnn (x followed by 3 numbers), this is the easiest, but least flexible.

The other way gives you each individual value in an array, so you can just nav to a specific array value, and get the xnnn that way.

[kellyalw]

will either method pick up on values that are subsequent to the "~" that delimits if there is >1 item purchased by one customer?

[TheDefender]

What you'd probably want to do at that point is to first explode using the ~ char, and then substr each value of the subsequent array...

So in the example you gave, explode would give you:

description1 : 1 : 15.50 : 1 : x003
description2 : 1 : 12.95 : 1 : x136

You could then use substr on each resultant array value, to get just the xnnn value.

[kellyalw]

I'm not sure where the hole is in my syntax - i've changed things so many times it's a blur. I'm trying to get it to display the two strings that you show prior to trying the substr. The output the following generates is just:
Array ( [0] => Resource id #4 )

<?PHP
include("config.php");
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database $DBName");
mysql_select_db($DBName) or die("Unable to select database $DBName");
$query = "SELECT cart FROM orders";
$result = mysql_query($query);
$cart = explode("~", $result);
print_r ($cart);
?>

[tsinka]

Hi,

you need to call mysql_fetch_array in order to get the rows:

<?PHP 
include("config.php"); 
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database $DBName"); 
mysql_select_db($DBName) or die("Unable to select database $DBName"); 
$query = "SELECT cart FROM orders"; 
$result = mysql_query($query); 

while ($row = mysql_fetch_array($result)) {
  $cart = explode("~", $row['cart']); 
  print_r ($cart); 
}
?>

[kellyalw]

cool - that worked perfectly
(I'm doing the happy dance!!!)

I added in the substr, but the output remains unchanged did I get the syntax wrong again?

<?PHP

include ("config.php");

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database $DBName");
mysql_select_db($DBName) or die("Unable to select database $DBName");
$query = "SELECT cart FROM orders";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
$cart = explode("~",$row['cart']);
substr($cart, -4);
print_r($cart);
}
?>

am I even doing the substr on the correct variable?

[tsinka]

Hi,

just a little error:

Replace

substr($cart, -4);

by

$cart = substr($cart, -4);

Substr doesn't manipulate the variable directly but returns the result of the substring operation.

Thomas

[kellyalw]

almost there! - prior to adding the substr the row returned was:

Array ( [0] => title3 : 1 : 10 : 0 : x003 ) Array ( [0] => title1 : 1 : 6 : 0 : x001 [1] => Matte title2 : 1 : 8 : 0 : x002 )

after adding the $cart = substr($cart, -4);

the output was:

rrayrray

so, it's obviously returning the last four letters of both words "Array" rather than the last four values from the three colon delimited fields. Am I going to need to explode by ":" afterall?

[tsinka]

Hi,

my fault ... sorry:

<?PHP 

include ("config.php"); 

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database $DBName"); 
mysql_select_db($DBName) or die("Unable to select database $DBName"); 
$query = "SELECT cart FROM orders"; 

$result = mysql_query($query); 

while ($row = mysql_fetch_array($result)) { 
$cart = explode("~",$row['cart']); 
foreach($cart as $key => $value) {
   echo substr($value, -4)."<br>\n"; 
}
print_r($cart); 
} 
?> 

Thomas

[kellyalw]

closer still, but still some extraneous values
the result was:

x003
Array ( [0] => title3 : 1 : 10 : 0 : x003 ) x001
x002
Array ( [0] => title1 : 1 : 6 : 0 : x001 [1] =>title2 : 1 : 8 : 0 : x002 )

[TheDefender]

take out the print_r($cart); line, and let's see what exactly is being output... It looks as though you are getting what you desire, so let's just see without printing out the arrays.

[kellyalw]

you're a genius!

output was

x003
x001
x002

just what I need!

For claification, when I do the IF statement, will I need any special syntax to compare all values returned to the number of the item being accessed from the thumbnail pic. The way I've figured it, I'll need one .php page for each thumbnail, since each thumbnail will link to an item with a different number. I won't need to express the item number as a variable, correct?
I guess what I'm asking is if the "==" will compare to all values returned.

if $cart =
x003
x001
x002

will the following return true without any special syntax:

IF ($cart == "x001")

(there, it's taken me 90 words to say 9)
thanks again for all your help - I've been beating my head against the desktop for almost two weeks.

[TheDefender]

Yes... you can use an IF comparison using the "==". Well, it won't actually output TRUE. But if it evaluates to TRUE, then you'd assign the specific actions to take...

[kellyalw]

outstanding. I've thought more about what I need to do. I believe that I can pass the item number information forward from the thumbnail in the URL - to allow me to have a single php page, rather than one php page for each item.
thanks again,
Kelly

[kellyalw]

alas, when I try to apply it, I'm again stumped.
do I need to assign a variable to =
substr($value, -4)
?
or are my echo statements at fault? (or both)
I've given up on passing the item number in the URL for now - so this would be a page specific for item 1 only.

<?PHP

include ("config.php");

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database $DBName");
mysql_select_db($DBName) or die("Unable to select database $DBName");
$query = "SELECT cart FROM orders";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
$cart = explode("~",$row['cart']);
foreach($cart as $key => $value) {
echo substr($value, -4)."<br>\n";
}
}
if (substr($value, -4) == "x001")
echo "../sold.html" target="self";
else
echo "../items/itemx001.html" target="self";
?>

</body>
</html>

[tsinka]

Hi,

this won't work but what about this:

include ("config.php"); 

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database $DBName"); 
mysql_select_db($DBName) or die("Unable to select database $DBName"); 
$query = "SELECT cart FROM orders"; 

$result = mysql_query($query); 

while ($row = mysql_fetch_array($result)) { 
  $cart = explode("~",$row['cart']); 
  foreach($cart as $key => $value) { 
    $value = substr($value,-4);
    if ($value == "x001") {
      echo "../sold.html\" target=\"_self\"";
    } else {
      echo "../items/itemx001.html\" target=\"_self\""; 
    }
  } 
} 
?> 

This just checks against x001.

I think the code can be improved (maybe by modifiying the SQL query a little bit). Assuming that you want the thumbnails to be replaced by "sold" images when an image has been sold already ... should the customers still be able to view the details of that item ?

Thomas

[kellyalw]

that code returned

../items/itemx001.html" target="self"../sold.html" target="self"../items/itemx001.html" target="_self"

rather than redirecting to either "sold.html" or "itemx001.html"

to answer your question, no - they don't need to see the item info it the item has already sold.
For now, I've created a generic sold.html page what will load and fill the frame that contains the thumbnail pics if the IF is equal - they should just pass on to the item description IF unequal

[kellyalw]

are the "" in the echo line OK? Seems like there's one too many in each...

[kellyalw]

OK, I've figured out that the echo statement is actually doing exactly what it's supposed to - echoing the text inside the "".
Is there a way to get an echo to physically redirect to a page, as a link would, or do I just have to include the the full page html inside the "" in the echo statement.