Crustyfur

ello, I am struggling with date functions as always and need some help. I just want to show results from the last 30 days. Thanks in advance. C SELECT COUNT(a.id), a.productid, p.productid, p.producttitle, p.productcode, p.manfid, m.manfid, m.manufacturer, DATE_FORMAT(a.datetime, '%D %M %Y') as a.datetime FROM tt_gear_afflinks_data a, tt_gear_product p, tt_gear_manufacturer m WHERE a.productid = p.productid AND p.manfid = m.manfid AND a.datetime = CURDATE() >= 30 GROUP BY a.productid ORDER BY COUNT(a.id) DESC LIMIT 20

Hi, Having problem get the nested query working and could use some help. I know that GROUP BY is probably causing the issue but I am not sure how to work around it. SELECT s.subcatid, s.subcategory, c.catid, p.catid, p.subcatid, p.active FROM tt_gear_subcategory s, tt_gear_category c, tt_gear_product p WHERE s.subcatid = p.subcatid AND c.catid = p.catid AND p.catid = '$catid' AND p.active = '1' GROUP BY s.subcatid ORDER BY s.subcategory ASC Second Query: $subcatid = $row["p.subcatid"]; $result2 = $db->sql_query("SELECT catid, subcatid, manfid, active FROM tt_gear_product WHERE catid = '$catid' AND subcatid = '$subcatid' AND active = '1' GROUP BY manfid");

Not sure I have the right title. I want to call results based on the first letter. e.g All cars in a list begining with 'A'. Any help appreciated. Thanks

Spot on thanks no_one.

Hi, I need the second query to display its results based on the 'townid' from the first query. I am not sure how to pass it. The page variable is '$countyid'. As you can see I have used a 'townid' of '11' as an example if I dont make any sense. <?php <table cellpadding=\"10\">"; $result = $db->sql_query("SELECT * FROM ft_clubs_town WHERE countyid = '$countyid' ORDER BY town ASC"); $counter = 0; echo "<tr><td valign=\"top\" align=\"left\">"; while($row = $db->sql_fetchrow($result)) { if($counter>25) { echo "</td><td valign=\"top\">"; $counter = 0; } echo $row["town"]; echo "<br />"; $result2 = $db->sql_query("SELECT club FROM ft_clubs WHERE townid = '11' ORDER BY club ASC"); while($row2 = $db->sql_fetchrow($result2)) { echo $row2["club"]; echo "<br />"; } echo "<br />"; $counter++; } echo "</tr>"; echo "</table> ?>

That did it. I was looking at it for so long I was blind to the obvious. Cheers Diego Chris

Is this a error cuased by lack of declaration of $db? <?php function newscommentsent() { global $pagetitle, $result; $pagetitle = "Fleet Town, Hampshire - News Comment Sent"; $result = $db->sql_query("INSERT INTO ".$prefix."_bus_pending (feildname) //--snip ?>

Here's the full job. <?php $result = $db->sql_query("UPDATE ".$prefix."_fixtures SET teamid='".forSql($_POST["teamid"])."',fdate='".forSql($_POST["fdate"])."', ftimeid='".forSql($_POST["ftimeid"])."', fopp='".forSql($_POST["fopp"])."', ftid='".forSql($_POST["ftid"])."', ffor='".forSql($_POST["ffor"])."', fagg='".forSql($_POST["fagg"])."' WHERE fid ='$fid'"); if($result) { echo "This fixture has been succesfully updated. I hope you won! "; } else { echo "<b>There has been an error adding the fixture!</b>"; print_r($db->sql_error($result)); } } list($fid,$teamid,$fdate,$ftimeid,$fopp,$ffor,$fagg,$ftid) = $db->sql_fetchinto("SELECT fid, teamid, fdate, ftimeid, fopp, ffor, fagg, ftid FROM ".$prefix."_fixtures WHERE fid= '$fid'"); echo "<br /><fieldset><legend>Edit Fixtures & Results</legend>"; $teams="<option> -- Select a Team --</option>"; $result = $db->sql_query("SELECT * FROM hh_teams ORDER BY teamid ASC"); while($row = $db->sql_fetchrow($result)) { $selected=""; if($row["teamid"]==$teamid) $selected = " selected=\"selected\""; $teams.="<option$selected value=\"".$row["teamid"]."\">".$row["team"]."</option>"; } $fixturetime="<option> -- Select a Time --</option>"; $result = $db->sql_query("SELECT * FROM hh_fixtures_time ORDER BY ftime ASC"); while($row = $db->sql_fetchrow($result)) { $selected=""; if($row["ftimeid"]==$ftimeid) $selected = " selected=\"selected\""; $fixturetime.="<option$selected value=\"".$row["ftimeid"]."\">".$row["ftime"]."</option>"; } $fixturetype="<option> -- Select a Fixture Type --</option>"; $result = $db->sql_query("SELECT * FROM hh_fixtures_type ORDER BY ftid ASC"); while($row = $db->sql_fetchrow($result)) { $selected=""; if($row["ftid"]==$ftid) $selected = " selected=\"selected\""; $fixturetype.="<option$selected value=\"".$row["ftid"]."\">".$row["ftype"]."</option>"; } $result = $db->sql_query("SELECT * FROM ".$prefix."_fixtures WHERE fid ='$fid'"); if($row = $db->sql_fetchrow($result)) { echo "$status<form action=\"$nukeurl/admin/?op=editfixture&fid=". $row[0] ."\" method=\"post\"> <br /> <center><b>This page allows you to edit fixtures & results.</b></center><br /><br /> <table width=\"100%\"> <tr><td align=\"right\"><b>Team: </b></td><td> <select id=\"teamid\" name=\"teamid\">$teams</select> <a href=\"http://www.heathhoops.com/admin/?op=addteam\">Add a New Team</a> </td></tr> <tr><td width=\"50%\" align=\"right\"><b>Date (YYYY-MM-DD): </b></td><td width=\"50%\" ><input type=\"text\" name=\"fdate\" id=\"fdate\" size=\"30\"/ value=\"". $row["fdate"] ."\"></td></tr> <tr><td align=\"right\"><b>Time: </b></td><td> <select id=\"ftimeid\" name=\"ftimeid\">$fixturetime</select> <a href=\"http://www.heathhoops.com/admin/?op=addtime\">Add a Time</a> </td></tr> <tr><td width=\"50%\" align=\"right\"><b>Opposition: </b></td><td width=\"50%\" ><input type=\"text\" name=\"fopp\" id=\"fopp\" size=\"30\"/ value=\"". $row[4] ."\"></td></tr> <tr><td align=\"right\"><b>Fixture Type: </b></td><td> <select id=\"ftid\" name=\"ftid\">$fixturetype</select> </td></tr> <tr><td align=\"right\"><b>Heath Hoops Score: </b></td><td><input type=\"text\" name=\"ffor\" id=\"ffor\" size=\"5\"/ value=\"". $row[5] ."\"></td><tr> <tr><td align=\"right\"><b>Opposition Score: </b></td><td><input type=\"text\" name=\"fagg\" id=\"fagg\" size=\"5\"/ value=\"". $row[6] ."\"></td><tr> <tr><td height=\"10\" colspan=\"2\"></td></tr> <tr><td align=\"center\" colspan=\"2\"><input type=\"submit\" name=\"savebutton\" value=\"Save Fixture\" /></td></tr> </table> </form></fieldset>"; ?>

As per subject. I have a form that allows users to edit entries in the db. Thats all cool. My problem is that if any fields are left blank the form is inserting a '0' value when I need it to remain 'null'. How do I stop it inserting '0' values? Thanks in advance. <?php <input type=\"text\" name=\"ffor\" id=\"ffor\" size=\"5\"/ value=\"". $row["ffor"] ."\"> ?>

Thanks Georg, That worked but I had to change the +1 to -1 ??? Works though Cheers

Hi guys, I am currently using a simple CURDATE() in my query: fdate > CURDATE() Is there a way of making it +1 day? At the moment it is updating the data 24 hours before I want it too. Cheers Chris

My bad, it was this lines fault. list(...) didnt match the sql. doh <?php list($fid,$teamid,$fdate,$ftime,$fopp,$ftid,$ffor,$fagg) = $db->sql_fetchinto("SELECT fid, teamid, fdate, fdate, fopp, ffor, fagg, ftid FROM ".$prefix."_fixtures WHERE fid= '$fid'"); ?>

just amended a minor error: <?php echo "<select id=\"teamid\" name=\"teamid\">$teams</select>"; echo "<select id=\"ftid\" name=\"ftid\">$fixturetype</select>"; ?>

Hi Guys, I have a problem with script below. First query works a treat, populating drop down and making the relevant entry default. For some reason the second drop down refuses to default. <?php list($fid,$teamid,$fdate,$ftime,$fopp,$ftid,$ffor,$fagg) = $db->sql_fetchinto("SELECT fid, teamid, fdate, fdate, fopp, ffor, fagg, ftid FROM ".$prefix."_fixtures WHERE fid= '$fid'"); $teams="<option> -- Select a Team --</option>"; $result = $db->sql_query("SELECT * FROM hh_teams ORDER BY teamid ASC"); while($row = $db->sql_fetchrow($result)) { $selected=""; if($row["teamid"]==$teamid) $selected = " selected=\"selected\""; $teams.="<option$selected value=\"".$row["teamid"]."\">".$row["team"]."</option>"; } $fixturetype="<option> -- Select a Fixture Type --</option>"; $result = $db->sql_query("SELECT * FROM hh_fixtures_type ORDER BY ftid ASC"); while($row = $db->sql_fetchrow($result)) { $selected=""; if($row["ftid"]==$ftid) $selected = " selected=\"selected\""; $fixturetype.="<option$selected value=\"".$row["ftid"]."\">".$row["ftype"]."</option>"; } ?> And the query in the form: <?php echo "<select id=\"teamid\" name=\"teamid\">$teams</select>"; echo "<select id=\"ftype\" name=\"ftype\">$fixturetype</select>"; ?> So $teams works but $fixturetype doesn't. It maybe be obvious to spot by my eyes are hurting now

Hey, I want to call data from the db and display it in a dropdown and make the selected option match the value of the record called. It thats clear? query <?php } $result = $db->sql_query("SELECT * FROM ".$prefix."_fixtures a, ".$prefix."_teams b WHERE a.teamid = b.teamid AND fid ='$fid'"); if($row = $db->sql_fetchrow($result)) { ?> Best I can muster <?php echo "<select name=\"teamid\"> <option value=\"". $row[8] ."\">". $row[9] ."</option> </select>"; ?> As you can see all it does is display the one option and not everything in the list. Thanks in advance.

Just when I thought it was safe. The website I am building is for a netball team. I have just been told their league scoring and it isn't as I expected. i.e. 3 points win 1 point draw and 0 points for a loss. It is in fact: 5 for a Win 3 for a Draw 1 for a loss but 50% of the score 0 for a loss below 50% of the score How do I get the below code to reflect the last two scoring options: SUM(CASE WHEN ffor > fagg THEN 3 WHEN ffor = fagg THEN 1 ELSE 0 END) as points Thanks again.

Thank you barand. I am very greatful. Chris

Famous last words.. Just a small bug. Above code is counting fixtures which not been played yet. How can I exclude these? Thanks again.

Barand, your a star. Works like a treat. THANKS!

Again I haven't been php'ing for long and I have hit a problem. I am querying a mysql db and I want to display some info based on the results of comparing feilds in another table. Part of the page with show a league table entry. Games played, how many have been lost, won or drawn and the for and against scores. Now I have half of this sorted and working, however, the part that works out how many games have been won lost or drawn is where I am at. Each game is stored in the db. It has the score for and the score against. So can php work out how many games have been W, L or D and make a count accordingly? This is where I am at. <?php echo "<table width=\"100%\" border=\"1\" cellpadding=\"3\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#111111\"> <tr> <td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Played</b></td> <td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Won</b></td> <td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Drawn</b></td> <td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Lost</b></td> <td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>For</b></td> <td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Against</b></td> <td bgcolor=\"#01CDFF\" width=\"22%\" align=\"center\"><b>League Points</b></td> </tr>"; $query = "SELECT COUNT(fid), SUM(ffor), SUM(fagg) FROM hh_fixtures WHERE ftype = '2'"; $result = mysql_query($query); if (mysql_num_rows($result) > 0) while($row = mysql_fetch_row($result)) { echo "<tr> <td width=\"13%\" align=\"center\">". $row[0] ."</td> <td width=\"13%\" align=\"center\"></td> <td width=\"13%\" align=\"center\"></td> <td width=\"13%\" align=\"center\"></td> <td width=\"13%\" align=\"center\">". $row[1] ."</td> <td width=\"13%\" align=\"center\">". $row[2] ."</td> <td width=\"22%\" align=\"center\"></td> </tr>"; ?>

Thanks chigley, I was sort of the right lines... :-\ heh Cheers

Hi, I am a relative newbie to php so please be kind I am trying to use the if statement to make a display based on comparing two values, pretty simple. Here's what I have: if($ffor > $fagg) echo " (L)"; elseif($ffor >= $fagg) echo " (D)"; elseif($ffor < $fagg) echo " (W)"; elseif(!empty($row[8])) echo " "; What I want it to do is not return any results when $ffar and $fagg are empty/null results. I am struggling as it keeps returning null values as a (D). Any help would be appreciated. Chris