Split section of php script from MySQL

j.smith1981 · August 24, 2009

Ok this is proving to be rather annoying for me, can you help me out?

I have a pick list/sheet for an application I am trying to write.

Its basic game is to group all products by the order id number.

GROUP_CONCAT by a selection to allow all products in that orderid to show basically.

Here's the full code I have done so far noting whats the problem:

<html>
<head>

<style type="text/css">

/* this is the important part (should be used in HTML head): */

.pagebreak {
    page-break-after: always;
}
</style>

</head>

<body>

<?php

$host = 'localhost'; // Sets the variable host as a literal no parsing for db connections
$user = 'root'; // username
$passwd = ''; // db password (abbreviated to unix password command)
$dbname = 'xcart';

$conn = mysql_connect($host, $user, $passwd) or die(); // makes a connection to the MySQL server

mysql_select_db($dbname); // Selects the actual database itself

// SQL query to retrieve picking ticket information
$db_query = "SELECT CONCAT(DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y'), ' ' ,DATE_FORMAT(FROM_UNIXTIME(A3.date), '%H:%i')) AS order_date, -- Displays date and time in 24hr format

-- Order information
A1.orderid AS orderid,

A3.status AS status,

-- Profile

A5.login AS username,

A5.title AS title,

A5.b_firstname AS f_name, -- Billing but using it as first name

A5.b_lastname AS l_name, -- Billing but using it as last name

-- Profile Billing
A5.b_address AS b_address,

A5.b_city AS b_city,

A5.b_zipcode AS b_pcode,

-- Profile Shipping
A5.s_address AS s_address,

A5.s_city AS s_city,

A5.s_zipcode AS s_pcode,


-- Products line THIS IS THE LINE I AM WANTING TO MAKE THE LIST OF PRODUCTS APPEAR IN THE SAME ORDERID, IF MANY ITEMS EXIST IN THE same orderid as it where

GROUP_CONCAT(A4.value) AS advent_code,
-- A4.value AS adventcode,

--A1.product AS descr,
GROUP_CONCAT(A1.product) AS descr,


GROUP_CONCAT(A1.amount) AS qty,

-- DO NOT GROUP_CONCAT PAST THIS - IF GROUP_CONCAT



-- Order notes
A3.customer_notes AS cust_notes




-- A1.product, A1.amount AS qty, DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y') AS order_date
FROM xcart_order_details A1
LEFT JOIN xcart_pricing A2 ON A1.productid = A2.productid
LEFT JOIN xcart_orders A3 ON A1.orderid = A3.orderid
LEFT JOIN xcart_extra_field_values A4 ON A1.productid = A4.productid
LEFT JOIN xcart_customers A5 ON A3.login = A5.login
WHERE A2.membershipid = '2' AND A3.status = 'P' AND A4.fieldid = '3'
GROUP BY A1.orderid
ORDER BY A5.login AND A1.orderid ASC";

$result = mysql_query($db_query); // makes a variable with the query assigned


while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{  // Opens up the function for the rest of the page to keep html highlighting!
?>




<table cellspacing="0" cellpadding="0" width="600" bgcolor="#ffffff">

<tr>
<td>
<table cellspacing="0" cellpadding="0" width="100%" border="0">
<tr>
<tr>
	<td valign="top"><br /><br /><img src="http://shopping.cartridgeworld.co.uk/skin1/images/cwNetsuiteLogo01.gif" alt="" /></td>
</tr>	
	<td width="100%">
	<table cellspacing="0" cellpadding="2" width="100%">
	<tr>

		<td valign="top">
<font style="FONT-SIZE: 28px"><b style="text-transform: uppercase;">X Cart Pick Sheet</b></font>
<br /><br />
<b>Date:</b> <?=$row['order_date'] ?><br /><b>Order id:</b> <?=$row['orderid'] ?><br /><b>Order status:</b> <?=$row['status'] ?><br />
<b>Payment method:</b><br />SEC Pay (Pay Point) (SECPay)<br /><b>Delivery method:</b><br />Royal Mail
		</td>

		<td valign="bottom" align="right">
<b>Cartridge World Ltd</b><br />
Unit A3, Hornbeam Square West, Hornbeam Park, Harrogate<br />
HG2 8PA, England<br />
United Kingdom (Great Britain)<br />
Tel: 01423 878520<br />Fax: 01423 878521<br />E-mail: [email protected]<br /><br />
<br />
		</td>

	</tr>
	</table>
	</td>
</tr>
</table>
<table cellspacing="0" cellpadding="0" width="100%" border="0">
<tr>
	<td><img height="2" src="/skin1/images/spacer.gif" alt="" /></td>
</tr>

<tr>
	<td bgcolor="#000000"><img height="2" src="/skin1/images/spacer_black.gif" width="100%" alt="" /></td>
</tr>
<tr>
	<td><img height="2" src="/skin1/images/spacer.gif" width="1" alt="" /></td>
</tr>
</table>
<br />
<table cellspacing="0" cellpadding="0" width="45%" border="0">

<tr>
	<td><b>Title:</b></td>
	<td>Mr.</td>
</tr>
<tr>
	<td nowrap="nowrap"><b>First Name:</b></td>
	<td>Jeremy</td>

</tr>
<tr>
	<td nowrap="nowrap"><b>Last Name:</b></td>
	<td>Test22</td>
</tr>
<tr>
	<td><b>Phone:</b></td>

	<td></td>
</tr>
<tr>
	<td><b>E-mail:</b></td>
	<td>[email protected]</td>
</tr>
</table>
<br />

<table cellspacing="0" cellpadding="0" width="100%" border="0">
<tr>
	<td width="45%" height="25"><b>Billing Address</b></td>
	<td width="10%"> </td>
	<td width="45%" height="25"><b>Ship To Alternative Address</b></td>
</tr>
<tr>

	<td bgcolor="#000000" height="2"><img height="2" src="/skin1/images/spacer_black.gif" width="100%" alt="" /></td>
	<td><img height="2" src="/skin1/images/spacer.gif" width="1" alt="" /></td>
	<td bgcolor="#000000" height="2"><img height="2" src="/skin1/images/spacer_black.gif" width="100%" alt="" /></td>
</tr>
<tr>
	<td colspan="3"><img height="2" src="/skin1/images/spacer.gif" width="1" alt="" /></td>
</tr>
<tr>
	<td>

	<table cellspacing="0" cellpadding="0" width="100%" border="0">
	<tr>
		<td><b>Address:</b> </td>
		<td><?=$row['b_address'] ?><br /></td>
	</tr>
	<tr>
		<td><b>City:</b> </td>

		<td><?=$row['b_city'] ?></td>
	</tr>
	<tr>
		<td><b>Postcode:</b> </td>
		<td><?=$row['b_pcode'] ?></td>
	</tr>
	</table>

	</td>
	<td> </td>
	<td>
	<table cellspacing="0" cellpadding="0" width="100%" border="0">
	<tr>
		<td><b>Address:</b> </td>
		<td><?=$row['s_address'] ?><br /></td>

	</tr>
	<tr>
		<td><b>City:</b> </td>
		<td><?=$row['s_city'] ?></td>
	</tr>
	<tr>
		<td><b>Postcode:</b> </td>

		<td><?=$row['s_pcode'] ?></td>
	</tr>
	</table>
        </td>
</tr>

<tr>
<td colspan="3"> </td>
</tr>

<tr>
<td width="45%" height="25"><b></b></td>
<td colspan="2" width="55%"> </td>
</tr>
<tr>
<td bgcolor="#000000" height="2"><img height="2" src="/skin1/images/spacer_black.gif" width="100%" alt="" /></td>
<td colspan="2" width="55%"><img height="2" src="/skin1/images/spacer.gif" width="1" alt="" /></td>
</tr>
<tr>
<td colspan="3"><img height="2" src="/skin1/images/spacer.gif" width="1" alt="" /></td>
</tr>

<tr>
<td><table cellspacing="0" cellpadding="0" width="100%" border="0">
<tr valign="top">
<td><b></b></td>
   	<td></td>
</tr>
</table></td>
<td colspan="2" width="55%"> </td>
</tr>




</table>
<br />
<br />
<!-- START OF PRODUCTS : HEADER SECTION -->
<table cellspacing="0" cellpadding="0" width="100%" border="0">

<tr>
<td align="center"><font style="FONT-SIZE: 14px; FONT-WEIGHT: bold;">Products ordered</font></td>
</tr>

</table>

<table cellspacing="0" cellpadding="3" width="100%" border="1">

<tr>
<th width="60" bgcolor="#cccccc">SKU</th>
<th bgcolor="#cccccc">Product</th>
<!-- <th nowrap="nowrap" width="100" bgcolor="#cccccc">VAT</th> -->
<!-- <th nowrap="nowrap" width="100" bgcolor="#cccccc" align="center">Item price</th> -->
<th width="60" bgcolor="#cccccc">Quantity</th>
<!-- <th width="60" bgcolor="#cccccc">Total<br /><img height="1" src="/skin1/images/spacer.gif" width="50" border="0" alt="" /></th> -->
</tr>
<!-- END OF PRODUCTS : HEADER SECTION -->


<tr>

<!-- START OF PRODUCTS : LIST -->

<?php
// Delimiters may be slash, dot, or hyphen
list $row['advent_code'] = split(",", $sku); // this is the problem
echo sku;

//list($month, $day, $year) = split('[/.-]', $date);
//echo "Month: $month; Day: $day; Year: $year<br />\n";
?>


<td align="center"><?=$row['advent_code'] ?></td>


<td><font style="FONT-SIZE: 14px"><?=$row['descr'] ?></font>
</td>




<td align="center"><?=$row['qty'] ?></td>

</tr>

<!-- CLOSING PHP OF PRODUCTS -->

</table>
<table cellspacing="0" cellpadding="0" width="100%" border="0">


<tr>
<td bgcolor="#000000" colspan="2"><img height="2" src="/skin1/images/spacer_black.gif" width="100%" alt="" /></td>
</tr>

</table>



</td>
</tr>


<tr>
<td align="center"><br /><br /><font style="FONT-SIZE:15px">Thank you for your order.</font></td>

</tr>

</table>
<br class="pagebreak" />

<?php
}
?>

</body>
</html>

As you can tell its a rather long one, but the noted part thats the problem is:

<?php
// Delimiters may be slash, dot, or hyphen
list $row['advent_code'] = split(",", $sku); // this is the problem
echo sku;

//list($month, $day, $year) = split('[/.-]', $date);
//echo "Month: $month; Day: $day; Year: $year<br />\n";
?>

Basically I want PHP to come back with rows of products for that order. Thats it plain and simple really but the split functions refusing to work, any ideas why?

Thanks in advance for any assistance,

Jeremy.

Catfish · August 24, 2009

split() returns an array.

you are assigning the split parts of $sku (split on character ,) to a single array index value, which in effect is a single variable.

list $row['advent_code'] = split(",", $sku);

is the same as:

$aSingleVariable = split(",", $sku);

afaik. Could be wrong on that though, since you are using the list construct. But if you do something like:

list ($row['advent_code'], $row['something_else'], ... etc) = split(",", $sku);

then you might get what you are looking for.

PS: You are also doing: echo sku; which should be echo $sku;

I just thought you can actually do: $row['advent_code'] = split(...); as it makes ['advent_code'] an array which would be then: $row['advent_code'][0] etc. so perhaps if you remove the list construct you might also get what you're looking for, depending on the format you want your data.

j.smith1981 · August 24, 2009

I cannot seem for the life of me to be able to get this to work, hmmm just having one of those days I think.

Is there a better function in php for getting back a result like:

HP100,HP200 etc (from MySQL)

To show up in HTML as:

<tr>

</tr>

<tr>

</tr>

What would you suggest?

I am really stuck at trying to convert examples and things, its just not clicking today.

Thanks for your help.

j.smith1981 · August 24, 2009

Just to report so to speak.

I have finally managed to get it working wasnt split I used it was explode by then delimiter, and it now works.

Many thanks for your help though, thought it made sense that split in logical thinking but explode into an array did it.

Wonderfull help though.

Thanks,

Jeremy.

Catfish · August 24, 2009

i saw explode in the manual also, but when i read on split it sounded like it should work anyway. good hear its working.

j.smith1981 · September 28, 2009

Got it working okish.

I was wanting though, for the split, at the moment I have it splitting by the comma into columns, and repeating for every record, but I have put a <hr> line below all columns to aid differentiation when the warehouse guys pick orders.

Is there anyway at the end of all this to to a <tr>?

I have the following at the moment you see:

<table cellspacing="0" cellpadding="3" width="100%" border="1">

<tr align="left">
<td bgcolor="#cccccc" width="12%" align="center"><font style="FONT-SIZE: 17pt"><b>Code</b></font></th>
<td bgcolor="#cccccc" align="center"><font style="FONT-SIZE: 17pt"><b>Product</b></font></th>
<td width="60" bgcolor="#cccccc"><font style="FONT-SIZE: 17pt"><b>Quantity</b></font></th>
<td width="60" bgcolor="#cccccc"><font style="FONT-SIZE: 17pt"><b>Picked</b></font></th>
<td width="60" bgcolor="#cccccc"><font style="FONT-SIZE: 17pt"><b>Checked</b></font></th>

<!-- <th width="60" bgcolor="#cccccc">Total<br /><img height="1" src="/skin1/images/spacer.gif" width="50" border="0" alt="" /></th> -->
</tr>
<!-- END OF PRODUCTS : HEADER SECTION -->


<tr>

<!-- START OF PRODUCTS : LIST -->


<td align="left" border=1>
<table>

<?php
// split advent code here for an example!

$advent_sku = $row['advent_code']; // sets a variable: 'advent_sku' = to advent_code in SQL (with comma values!)

$advent_new_sku = explode(",", $advent_sku);
foreach($advent_new_sku as $product_code){
echo "<tr><td>$product_code<hr></td></tr>";
}

?>

</table>

</td>


<td>



<table>

<?php
// split advent code here for an example!

$advent_descr = $row['descr'];

$advent_new_descr = explode(",", $advent_descr);
foreach($advent_new_descr as $product_descr){
echo "<tr><td>$product_descr<hr></td></tr>";
}

?>

</table>
</td>




<td align="center">


<table>

<?php
// split advent code here for an example!

$advent_qty = $row['qty'];

$advent_new_qty = explode(",", $advent_qty);
foreach($advent_new_qty as $product_qty){
echo "<tr><td>$product_qty<hr></td></tr>";
}

?>

</table>


</td>

<td align="center">


<table>

<?php
// split advent code here for an example!

$advent_qty = $row['qty'];

$advent_new_qty = explode(",", $advent_qty);
foreach($advent_new_qty as $product_qty){
echo "<tr><td> </td></tr>";
}

?>

</table>
<table>

<?php
// split advent code here for an example!

$advent_qty = $row['qty'];

$advent_new_qty = explode(",", $advent_qty);
foreach($advent_new_qty as $product_qty){
echo "<tr><td></td></tr>";
}

?>

</table>

<table>

<?php
// split advent code here for an example!

$advent_qty = $row['qty'];

$advent_new_qty = explode(",", $advent_qty);
foreach($advent_new_qty as $product_qty){
echo "<tr><td><hr></td></tr>";
}

?>

</table>


</td>

</tr>

<!-- CLOSING PHP OF PRODUCTS -->

</table>

I would appreciate some examples or if you know a way of making this work that would be wonderful thanks in advance too,

Jeremy

Sign In

Split section of php script from MySQL

Recommended Posts

j.smith1981

Link to comment

Share on other sites

Catfish

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

Catfish

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information