Barand

I do it the laborious way. I have often thought of creating a tool (in fact every time I create a new diagram)

If it exists then use it. You have the table that associates HUN with hassal132.gif, so why put "hassal_gif" into another 5000 records? You only need it the once. That is whole point of using a relational database. http://lmgtfy.com/?q=data+normalization

Are you processing the results from the first query before you call and process the second query? If not, the results from the second overwrite the results from the first.

But your temperature values are not int (eg 21.06, 19.69)

Don't do that. Have a "flag" table which contains records with code and image name for each country. Job done. | tm_country | image_name | +---------------+------------------+ | HUN | hassal-132.gif | When you query the first table, JOIN to the flag table using the country code

Management has a singlw image so that can go in the management table Services have many images so you need a table to store the images for each service, 1 image per row linked by the service id. +---------------+ | management | +---------------+ | id | | name | | designation | | description | | added_date | | profile_img | +---------------+ +---------------+ +-----------------+ | services | | service_image | +---------------+ +-----------------+ | id |----+ | serv_img_id | | name | +---<| service_id | | description | | service_image | | added_date | +-----------------+ +---------------+

http://php.net/manual/en/mysqli.query.php

I have a suspicion that approach would send the same notifications twice on the same day in some instances but the way to find out is to set up some test data and see.

It gets a bit more complicated but here goes SELECT CASE WHEN thiswk.rank > prevwk.rank THEN '^' WHEN thiswk.rank < prevwk.rank THEN 'v' ELSE '-' END as movement , thiswk.rank as this_rank , prevwk.rank as prev_rank , users.firstname , users.surname , tot.overall , thiswk.score FROM users LEFT JOIN ( -- subquery for overall total SELECT userID , SUM(score) as overall FROM points GROUP BY userID ) as tot USING (userID) LEFT JOIN ( -- subquery for this week rank and score SELECT userID , @seqa := @seqa+1 as seq , @ranka := IF(score=@prevscorea, @ranka, @seqa) as rank , @prevscorea := score as score FROM points JOIN (SELECT @prevscorea:=null,@seqa:=0,@ranka:=null) as inita WHERE week = (SELECT MAX(week) FROM points) ORDER BY score DESC ) as thiswk USING (userID) LEFT JOIN ( -- subquery for prev week rank SELECT userID , @seqb := @seqb+1 as seq , @rankb := IF(score=@prevscoreb, @rankb, @seqb) as rank , @prevscoreb := score as score FROM points JOIN (SELECT @prevscoreb:=null,@seqb:=0,@rankb:=null) as inita WHERE week = (SELECT MAX(week)-1 FROM points) ORDER BY score DESC ) as prevwk USING (userID) ORDER BY thiswk.score DESC ; sample results +----------+-----------+-----------+-----------+---------+---------+-------+ | movement | this_rank | prev_rank | firstname | surname | overall | score | +----------+-----------+-----------+-----------+---------+---------+-------+ | - | 1 | 1 | B | Bbb | 8 | 3 | | - | 2 | 2 | C | Ccc | 6 | 2 | | v | 3 | 4 | A | Aaa | 5 | 1 | | ^ | 3 | 2 | D | Ddd | 5 | 1 | +----------+-----------+-----------+-----------+---------+---------+-------+

There were times when thought this was going to be a job for life :-)

or $enemyNames = array("The Pain", "The Fear", "The End"); $enemyImage = array("img01", "img02", "img03"); $combined = array_combine($enemyNames, $enemyImage); $key = array_rand($combined); echo "$key - {$combined[$key]}";

You get those undefined errors because those fields were not selected in the query and therefore not in the results Try this $sql = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_firstline , visitor_secondline , visitor_town , visitor_county , visitor_postcode , visitor_tel , visitor_mobile , visitor_model , visitor_plate , DATE_FORMAT(MAX(IF(item_id=2,renewal_date,0)), '%e %M %Y') as motdue , DATE_FORMAT(MAX(IF(item_id=3,renewal_date,0)), '%e %M %Y') as taxdue , DATE_FORMAT(MAX(IF(item_id=1,renewal_date,0)), '%e %M %Y') as insdue , DATE_FORMAT(MAX(date_notified), '%e %M %Y') as date_notified FROM visitor v INNER JOIN renewal USING (visitor_id) GROUP BY v.visitor_id"; $query = mysqli_query($db, $sql) or die (mysqli_error($db)); if($result = mysqli_query($db, $sql)){ if(mysqli_num_rows($result) > 0){ echo "<div id='column-whole-main'>"; echo "<table cellspacing='0'>"; echo "<thead>"; echo "<tr>"; echo "<th>ID</th>"; echo "<th>Name</th>"; echo "<th>Email</th>"; echo "<th>Address Line 1</th>"; echo "<th>Address Line 2</th>"; echo "<th>Town</th>"; echo "<th>County</th>"; echo "<th>Postcode</th>"; echo "<th>Telephone Number</th>"; echo "<th>Mobile Number</th>"; echo "<th>Vehicle Model</th>"; echo "<th>Vehicle Number Plate</th>"; echo "<th>Vehicle Tax Expiry Date</th>"; echo "<th>Vehicle MOT Expiry Date</th>"; echo "<th>Vehicle Insurance Expiry Date</th>"; echo "<th>Email Sent</th>"; echo "</tr>"; echo "</thead>"; echo "<tbody>"; //fetch the data from the database while ($row = mysqli_fetch_array($query)) { echo "<tr>"; echo "<td>" . $row['visitor_id'] . "</td>"; echo "<td>" . $row['visitor_name'] . "</td>"; echo "<td>" . $row['visitor_email'] . "</td>"; echo "<td>" . $row['visitor_firstline'] . "</td>"; echo "<td>" . $row['visitor_secondline'] . "</td>"; echo "<td>" . $row['visitor_town'] . "</td>"; echo "<td>" . $row['visitor_county'] . "</td>"; echo "<td>" . $row['visitor_postcode'] . "</td>"; echo "<td>" . $row['visitor_tel'] . "</td>"; echo "<td>" . $row['visitor_mobile'] . "</td>"; echo "<td>" . $row['visitor_model'] . "</td>"; echo "<td>" . $row['visitor_plate'] . "</td>"; echo "<td>" . $row['taxdue'] . "</td>"; echo "<td>" . $row['motdue'] . "</td>"; echo "<td>" . $row['insdue'] . "</td>"; echo "<td>" . $row['date_notified'] . "</td>"; echo "</tr>"; } echo "</table>"; echo "</div>"; // Close result set mysqli_free_result($result); } else{ echo "No records matching your query were found."; } } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($db); }

phpmyadmin of course.

If you are "using mySql and a loop to generate each array" how are you ending up with the string $arrayOutSideTempData? Probably better if you post the code so we can see what you are doing (use the <> button in the toolbar to post code)

You may not be seeing anything because the date_notified values have been updated so no records are now selected by the query. Try resetting the renewals table UPDATE renewal SET date_notified = NULL; and check if no records found $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitor v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); if ($query->num_rows==0 ) { // ADD echo "No reminders due"; // THESE } // LINES

Is the mail() call working OK? Are you getting the success or error messages output?

Is the mail() call working OK? Are you getting the success or error messages output?

No - not right. The $count=1 needs to be before the loop otherwise it gets set to 1 for every record. You should only process the count on change of visitor, when email sent. You are currently counting every record and there could be several records per email.

Your email count needs to be in the main loop. At the moment it is only counting the final email message.

Then append them to the message $message = $_POST['message'] . "\n" . $_POST['name'] . "\n" . $_POST['email'] . "\n" . $_POST['telephone'];

Create a table subquery to get the max values for each track/race and join to that. Where speed matches maxspeed then output as bold (not tested) SELECT t1.Horse , t1.Track , t1.Race , t2.Speed , t2.Class , t2.Form , t2.Last , t2.Conn , t2.Line , t2.win , t2.Total , maxspeed , maxclass , maxform , maxconn , maxtotal FROM racestoday as t1 LEFT JOIN horses as t2 ON t1.HorseRef = t2.HorseRef LEFT JOIN ( SELECT track , race , MAX(Speed) as maxspeed , MAX(Class) as maxclass , MAX(Form) as maxform , MAX(Conn) as maxconn , MAX(Total) as maxtotal FROM racestoday WHERE date=CURDATE() JOIN horses USING (HorseRef) GROUP BY track, race ) as maxvals USING (track, race) WHERE t1.Date = CURDATE() ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC

An alternative approach to the problem might be instead of calling the chart for the pup, call it twice - once for the sire and again for the dam and output the two charts one above the other

There are several lines per visitor, each date on a separate row in the results. You have to loop through the rows to process the dates but only output and send the email when the visitor id changes. Note I added the renewal_id to the query for subsequent updating of the notification dates (in a single update query at the end) $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitor v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($conn, $sqlCommand) or die (mysqli_error($conn)); $current_visitor=0; $current_email = ''; $headers = "From: [email protected]\r\n"; $subject = "Expiry Date(s)"; $message = ''; $renewals = array(); $notifications = array(); //fetch the data from the database while ($row = mysqli_fetch_array($query)) { // has visitor_id changed if ($row['visitor_id'] != $current_visitor) { // send email to current visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); echo "<b>Email Successfully Sent</b><br><br>"; // success, so add renewal ids to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } $current_visitor = $row['visitor_id']; $current_email = $row['visitor_email']; $message = "Name: {$row['visitor_name']} \n\n"; $renewals = array(); } $message .= "{$row['description']} expiry date: {$row['datedue']}\n"; $renewals[] = $row['renewal_id']; // store renewal id for updates } // send email to final visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); echo "<b>Email Successfully Sent</b><br><br>"; // success, so add to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } // update successful notifications $idlist = join(',', $notifications); $sql = "UPDATE renewal SET date_notified = NOW() WHERE renewal_id IN ($idlist)"; $conn->query($sql) ;

Don't put quotes around the userid in url echo "<br><br><a href='visitDetails.php?userid='" . $userid . "'>Visit Results</a>";?> | | +-- href ends here -----+

You don't have columns "taxdate", "motdate", "vidate" in the query results (see my prev reply ^ ) As for date_notified, what have you called the column in the renewal table?