How to repeat data from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

How to repeat data from MySQL

Post by micknc »

I have built a report in php and I have hit a wall. Here is a drastically cut down version of my form:

Code: Select all

 
$query = "SELECT * FROM LINEITM, CUSTSHP WHERE LINEITM.SHIP_ID = CUSTSHP.SHIP_ID AND LINEITM.SO_NO=$id";
 
$row['NAME'];
$row['ADDRESS'];
 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['QTY'];
$row['DESCRIP'];
}
echo "footer.php"
 
I really want to change SO_NO=$ID to SO_NO between $id1 and $id2.
The problem is if I do that I would get a whole bunch of product listings in my array.
I really need to repeat the entire form over with a page break (html to format printing).

I have been thinking and reading about maybe limiting the query to one and then loop back through it adding one to the id until I get to the id2.
Am I on the right track.

Is there a way to do what I want? I am new but learning so point me in the right direction.
Thanks,
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to repeat data from MySQL

Post by Christopher »

Code: Select all

 
$query = "SELECT * FROM LINEITM, CUSTSHP WHERE LINEITM.SHIP_ID = CUSTSHP.SHIP_ID AND LINEITM.SO_NO>=$id1 AND LINEITM.SO_NO<=$id2";
// OR
$query = "SELECT * FROM LINEITM, CUSTSHP WHERE LINEITM.SHIP_ID = CUSTSHP.SHIP_ID AND LINEITM.SO_NO BETWEEN $id1 AND $id2";
(#10850)
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: How to repeat data from MySQL

Post by micknc »

Yes I know the between query but the problem lies in the array. The query above is going to result in the first listing as the customer name and address and then all the contents of line.itm as the array (customer 2, 3, and 4's ordered products).

It seems like I have to loop it in some way to produce an array for each instance of my customer.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to repeat data from MySQL

Post by Christopher »

Huh? Maybe you should show your tables structures and some example data to show what you mean.
(#10850)
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: How to repeat data from MySQL

Post by micknc »

lineitm has the items that were ordered here is what the table would look like with two orders in it:

Fields
order_no, qty, description, cust_id
100, 1, toaster, 12
100, 3, oven, 12
100, 2, light bulbs, 12
101, 1, toaster, 13
101, 1, table, 13

Customer number 12 has ordered 3 items on order number 100 and customer 13 has two items on order 101

The customer info is store in the custshp table

Fields
cust_name, cust_address, cust_id
Joe, 123 anywhere ave, 12
Jane, 987 main st, 13

So my form would get so_no 100 and report the following:

Joe
123 anywhere ave

Qty Description
1 toaster
3 oven
2 light bulbs

That is working fine. The problem I am having is producing multiple forms at one time. (I need to be able to print more than one at a time) so I thought I would throw a page break in there and print two of them. If I use the between function (between 100 and 101) as is I would get the following:
Joe
123 anywhere ave

Qty Description
1 toaster
3 oven
2 light bulbs
1 toaster
1 table

So I think I need to use a loop function. Since my first post I have been trying that but still no dice.

Does that clarify what I am trying to do?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to repeat data from MySQL

Post by califdon »

micknc wrote:

Code: Select all

$query = "SELECT * FROM LINEITM, CUSTSHP WHERE LINEITM.SHIP_ID = CUSTSHP.SHIP_ID AND LINEITM.SO_NO=$id";
 
[color=#FF0000]$row['NAME'];[/color]
[color=#FF0000]$row['ADDRESS'];[/color]
 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['QTY'];
[color=#FF0000]$row['DESCRIP'];[/color]
}
echo "footer.php"
 
I really want to change SO_NO=$ID to SO_NO between $id1 and $id2.
The problem is if I do that I would get a whole bunch of product listings in my array.
I really need to repeat the entire form over with a page break (html to format printing).

I have been thinking and reading about maybe limiting the query to one and then loop back through it adding one to the id until I get to the id2.
Am I on the right track.

Is there a way to do what I want? I am new but learning so point me in the right direction.
Thanks,
The lines in red above do absolutely nothing. Remove them.

As arborint said, use either BETWEEN or <= and >= in the WHERE clause to select the records you want. That gives you the data you need. Then you must write a PHP loop that tests for a change in SO_NO and starts a new page. You can't do that in your SQL.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: How to repeat data from MySQL

Post by micknc »

This is working okay but it could be improved on.

I saved my report as report.php
then use this code as loop.php:

Code: Select all

 
<?
$start=$_GET['start'];
$end=$_GET['end'];
 
$num = $start;
while ( $num <=$end )
{
include 'report.php';
print $num . "";
$num = $num + 1;
}
?>
 
Pass the start and end variable with an html form to loop.php and inside my report.php I use lineitm=$num and put a page break as the last line of the file.
It works but is it the best way?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to repeat data from MySQL

Post by califdon »

micknc wrote:This is working okay but it could be improved on.

I saved my report as report.php
then use this code as loop.php:

Code: Select all

 
<?
$start=$_GET['start'];
$end=$_GET['end'];
 
$num = $start;
while ( $num <=$end )
{
include 'report.php';
print $num . "";
# $result = mysql_query($query) or die(mysql_error());
# while($row = mysql_fetch_array($result)){$num = $num + 1;
}
?>
 
Pass the start and end variable with an html form to loop.php and inside my report.php I use lineitm=$num and put a page break as the last line of the file.
It works but is it the best way?
Well, personally, I wouldn't break up the script into such small separate files. That's bound to slow performance in order to open and include several small files, although in most applications the difference would probably not be noticeable.

But if report.php is what you first showed us, I don't see how it can work, since what you need to do is run the query once, then loop in PHP through the result set, looking for a change in SO_NO. Your loop.php doesn't appear to do that. I see no need to use more than one small php file. It shouldn't require more than a dozen or so lines of code, altogether.

It would look roughly like this:

Code: Select all

$query = "SELECT * FROM lineitm, custship ";
$query .= "WHERE lineitm.ship_id = custship.ship_id AND lineitm.so_id=$id AND custship.ship_id BETWEEN $first AND $last";
$result = mysql_query($query) or die(mysql_error());
$last_so = "";
while($row = mysql_fetch_array($result)) {
    if ($row['so_id'] != $last_so) {
        echo "<strong>" . $row['so_id'] . "</strong><br />";
        // ... and cust_name, etc.
    }
    echo "&nbsp; &nbsp;" . $row['qty'] . "&nbsp; &nbsp;" . $row['descrip'] . "<br />";
    $last_so = $row['so_id'];
}
 
Post Reply