select multiple rows

[Slarti]

I thought this should be ease but I ain't any hair left!!

 

I have a MYSQL table with the following columns.

 

|- ID -|- STRTOTIME -|- COST -|

 

ID = 1 to 365, STRTOTIME = the PHP strtotime for every day in the year, and COST = the daily hire cost

 

I can find the 'daily hire cost' by using:-

 

SELECT cost FROM table WHERE strtotime = $first_date ($first_date is stored in another table)

 

I can then display it easily:-  echo ($result * 7)

 

My problem is we have 9 "hire seasons" and I'd like to display the 'weekly hire costs' for all 'hire seasons'.

 

At the moment I have it working by using 9 separate SELECT statements. But surly this can be done using only one SELECT statement, can't it??

 

Any help would be much appreciated.

[joel24]

could you use a group by in your sql query to group it by weeks?

SELECT SUM(cost) 
FROM table 
WHERE strtotime BETWEEN $first_date AND $last_date
GROUP BY WEEK(from_unixtime(strtotime))

 

otherwise select it all into PHP and use PHP to iterate through each and have an array with totals, and add the cost to the corresponding array index...

$query="SELECT cost, strtotime FORM table WHERE strtotime BETWEEN $first_date AND $last_date";
$weeks=array();
WHILE ($row=mysql_fetch_assoc($query)){
$currentWeek=date('W',$row['strtotime']);
$weeks[$currentWeek]+=$row['cost'];
}

 

have a play around... should help with the groundwork?

[Slarti]

Thanx for your reply. I seems that I didn't quite explain my problem clearly.

 

I wanted to select several rows, from the 365 rows the table contains, and not sequentiality as your select statement does.

 

I've now sussed it using:-

 

$a = '1293836400';
$b = '1301608800';

$query = "SELECT cost FROM DB_table WHERE strtotime IN( '$a','$b')";
$result = mysql_query($query, $db_name) or die(mysql_error());
if (mysql_num_rows($result) == 0) {
    echo "No rows found, exiting";
    exit;
}
while ($row = mysql_fetch_assoc($result)) {
    echo $row["cost"].;
}

I had tried this select statement before with no luck but a little tweaking and it works a treat.

 

A supplementary question.

 

Is running the $result through a while loop the only way to 'echo' the results?

 

Is it possible to ...

 

echo FIRST $row["cost"].;

 

<some html code>

 

echo SECOND $row["cost"].;

 

<some html code>

 

ect.ect....

 

Thanks for your time.

[joel24]

the easiest way is to loop through it in a while loop, though you can use mysql_result() to call one row...

 

//from php.net
$result = mysql_query('SELECT name FROM work.employee');
if (!$result) {
    die('Could not query:' . mysql_error());
}
echo mysql_result($result, 2); // outputs third employee's name

## or you could do this instead
$result = mysql_query('SELECT name, email FROM work.employee');
if (!$result) {
    die('Could not query:' . mysql_error());
}
echo 'name: '.mysql_result($result, 2,'email'); // outputs third employee's email
echo 'email: '.mysql_result($result, 2,'name'); // outputs third employee's name

[fenway]

I'm sorry, you want to group by what?

[Slarti]

Thank you joel24

echo 'name: '.mysql_result($result, 2,'email');

Is exactly what I've been looking for This is going to make things a lot easier, thanks again.

 

 

[fenway]

Yes, but mysql_result() is very slow.

[Slarti]

Yes, but mysql_result() is very slow.

 

So this implies that as I've often found there are other, sometimes better ways, would you care to share?

 

My Select statement queries 6 columns and 9 rows

 

$thisyear = date("Y");
$nextyear = (date("Y")+1);


$query = "SELECT cost_1, cost_2, cost_3, cost_4, cost_5, cost_6 FROM cost_$thisyear 
WHERE date_strtotime IN( '$date_1','$date_2','$date_3','$date_4','$date_5','$date_6','$date_7','$date_8','$date_9')";
$result = mysql_query($query, $carhire) or die(mysql_error());

The above will be duplicated for $nextyear and as said I'd like to

 

echo FIRST $row["cost"].;

 

<some html code>

 

echo SECOND $row["cost"].;

 

<some html code>

 

ect.ect....

 

[Wiro Blangkon]

$query = "SELECT cost_1, cost_2, cost_3, cost_4, cost_5, cost_6 FROM cost_$thisyear 
WHERE date_strtotime IN( '$date_1','$date_2','$date_3','$date_4','$date_5','$date_6','$date_7','$date_8','$date_9')";

Being a person that produces structure in other people's lives within a time limit, I'm a big fan of straightforward and manageable designs. That said: the purpose of your raw costs table is to keep the raw costs. If you want to give some special status to some costs, for example hire seasonly costs, you can create a table that combines the hire seasons with the cost ids. That latter table would have a different purpose from your raw cost table, namely adding a special status to your raw costs and most likely make it easier to get the costs given a specific hire season.

 

Is it very important that the weekly costs per hire season (or do you mean AVERAGE weekly costs per hire season?) are extracted purely with MySQL? You would make your life easier by first extracting the weekly costs from the raw costs with a MySQL query, and then extracting the weekly costs per hire season with PHP:

 

raw costs (table) >>> weekly costs (MySQL query) >>> hire season costs (PHP)

 

Please let us know if you need help with the query or the implementation.

 

Best regards,

Wiro Blangkon.

[fenway]

mysql_fetch_assoc() will give you the hash you seek.

[Slarti]

Food for thought Wiro. My knowledge of Mysql and php is very limited, saying that I have managed to update a friends holiday car hire site with a 'Hire cost calculator, which works 100% (saves several 000 "How much" e-mails each year ) . You choose your car, pick the dates and the full cost is displayed. I'm now going back over it looking for better ways to get what I need.

 

Basically it works by storing a daily rate for each day of the year. A query selects the 'daily rate' for all dates wanted then I do a php array sum to get the total. I am looking at the Mysql SELECT SUM which may be better. Or is there another better way?

 

My original question was to get the daily cost on the first day for each of the 'Seasons' and (display * 7) them on the management page, as a visual check.

 

fenway I like your answers best way to learn, plant the seed and let me do the working out.

[Wiro Blangkon]

Hello Slarti,

 

Good to know you appreciate my questions. You can protect yourself from a lot of time in re-programming, undoing previous work, if you know what your goal is and how to get there. Since you're saying you are making your first steps in PHP and MySQL, I'd say: keep things as simple, plain and straightforward as possible. You can think about optimization of speed, memory and sheer beauty of code once you feel you're ready for it.

 

Storing a rate for each day of the year sounds inefficient storage to me, but if there's a maximum of 366 rates in the rate table, that would be no problem. Besides, if the rates are stored as a simple, 366-record table, extraction of rates for each renting agreement would be a simple matter of selecting which rates are used.

 

Here's some food for thought:

SELECT ac.agreement_id, SUM(r.rate) AS total_cost
FROM agreements_costs ac
INNER JOIN ac.rate_id=r.id
WHERE ac.agreement_id=<enter your agreement id here>

 

Good luck!

 

Wiro Blangkon.