PHP query - should I use IF or EMPTY?

Question

So I have two tables - Horses: List horses information Results: Lists results of shows with horses in them

I have a view: v_testhorses: has information from Horses listed, then calculates amount of 1st (wins), 2nds (show), 3rds (places) and earnings of a horse per YEAR. For each YEAR there's a different line in the table, so that the earnings, places, etc. can be displayed by year

I'm trying to query this on a search result page and I'm having trouble figuring out HOW to query this information. Ideally it'd be into an HTML table like so- Year - wins - place - shows - LTE

However, not EVERY horse will have shown in 2017, 2016, etc. so I would like for those years to be blank

Current code:

$data=mysql_query("SELECT * FROM `v_testhorses` WHERE `year`=2017"); $row=mysql_fetch_object($data); echo mysql_error(); echo"<tr><td>$row->year</td>"; echo"<td>$row->wins</td>"; echo"<td>$row->place</td>"; echo"<td>$row->show</td>"; echo"<td>$" . number_format($row->LTE) . "</td></tr>";

-I've tried to have the "echo" with isset, empty, etc and no matter which of the million ways I try it, I get some sort of error. Either it crashes my entire page or it does nothing -I've tried switching my query to an IF statement (IF year = 2015, for example) to no avail

QUESTION: 1. How should I query this where if there's no entry for the horse when year = 2015 it just doesn't pull anything up? 2. Is there an easier way to query this than copy and pasting the above code (or the fixed version of it!) over and over for every year 2007-2017? And so when 2018 rolls around I don't have to update the code. (many thanks in advance!)

Don't use the deprecated and insecure mysql*-functions. They have been deprecated since PHP 5.5 (in 2013) and were completely removed in PHP 7 (in 2015). Use MySQLi or PDO instead. 2. You are wide open to SQL Injections and should really use Prepared Statements instead of concatenating your queries, which can be used if you use the above mentioned MySQLi or PDO. — Milan Chheda
– Milan Chheda, Commented Sep 9, 2017 at 11:33

Tomasz W. · Accepted Answer · 2017-09-09 12:08:19Z

You should use while which prevents from printing when there are no records in db:

while ($row = mysql_fetch_object($data)) { echo"<tr><td>$row->year</td>"; echo"<td>$row->wins</td>"; echo"<td>$row->place</td>"; echo"<td>$row->show</td>"; echo"<td>$" . number_format($row->LTE) . "</td></tr>"; }

If you want to fetch from 2007-2017 I recommend one query which select all year since 2007 (which also solves your problem with 2018 year)

$data=mysql_query("SELECT * FROM `v_testhorses` WHERE `year` >= 2007");

The last thing which is worth mentioning (and it was pointed in the comments) is that its better to use MySQLi as you used deprecated functions. http://php.net/manual/en/mysqli-result.fetch-object.php

Now that I'm playing with the "While" option, I've noticed it doesn't fetch the youngest data. The earliest year I have thus far is 2013, and it will fetch all data 2014-2017. Long shot: any idea why?
Could you show to me your sql query or at least where statement ?
Please, write also the type of the field year in the database ?
while ($row = mysql_fetch_object($data)) { echo"<tr><td>$row->year</td>"; echo"<td>$row->wins</td>"; echo"<td>$row->place</td>"; echo"<td>$row->show</td>"; echo"<td>$" . number_format($row->LTE) . "</td></tr>"; } When I created the "year" value for my table view I used: GROUP BY h.hname, DATE_FORMAT(STR_TO_DATE(r.Date, '%c/%e/%y'), '%Y');

Collectives™ on Stack Overflow

PHP query - should I use IF or EMPTY?

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related