MySQL query questions - can you use variables in the WHERE statement?

[oh_maestro]

I run a high school site and am learning to use PHP/MySQL, and I have two problems that I cannot figure out.

1. On our sports scoreboard, I have very repetative code, that I would like to make into a function.
   I used Dreamweaver to query the info (I have 10 similar queries on this page)
   mysql_select_db($database_wintersports, $wintersports);
   $query_BVBB_Result = "SELECT * FROM 0405 WHERE Level LIKE 'Varsity' AND Sport LIKE 'Basketball' AND Gender LIKE 'Boys' AND (Result != '' OR InvTotal > 0) ORDER BY 'Date' DESC";
   $BVBB_Result = mysql_query($query_BVBB_Result, $wintersports) or die(mysql_error());
   $row_BVBB_Result = mysql_fetch_assoc($BVBB_Result);
   $totalRows_BVBB_Result = mysql_num_rows($BVBB_Result);

Here is the current code to display the info:
<?php
if ($row_BVBB_Result['Opponent'] != "" ){
echo '~ '.date('n/j',strtotime($row_BVBB_Result['Date'])) ?>
<br>
<?php if ($row_BVBB_Result['Location'] == "Away")
$vs = "@ ";
else
$vs = "vs. ";?>
<?php if ($row_BVBB_Result['InvPlace'] == 0)
echo $vs.($row_BVBB_Result['Opponent'])." ".($row_BVBB_Result['Result'])." ".($row_BVBB_Result['Pts'])."-".($row_BVBB_Result['Pts_Opp']); }?>

This works fine, but I would like to create function that will need just the sport, level, or gender as a variable to run the function. I have tried the following with no success.

<?php
function Results($Sport){
$Sport = '$row'.$Sport.'Result';
if ($Sport['Opponent'] != "" ){
echo '~ '.date('n/j',strtotime($Sport['Date'])).'<br>';
if ($Sport['Location'] == "Away")
$vs = "@ ";
else
$vs = "vs. ";
if ($Sport['InvPlace'] == 0)
echo $vs.($Sport['Opponent'])." ".($Sport['Result'])." ".($Sport['Pts'])."-".($Sport['Pts_Opp']);
echo "<br>".$Sport; }
}?>

The calling code is:
<?php $Sport="VBBB";
Results($Sport);
?>
When I run this and echo $Sport, it says $row_VBBB_result, which should work. Any suggestions?

2. I want to make a Happy Birthday table that will pull a student's birthday from SQL and display their name.

I want to query today's date (but not year) from SQL using the field name BIRTHDAY. (I can set up the data type anyway I need to, including TEXT) Any ideas?

[marian]

is this function Result supposed to return a value?

function Result($sport='')
{
return $sport;
}

and where will you call this value? is it going to be in your sql query? try breaking down question 1 more:

question 2:

you can do like this:

select * from table where birthday like '1208%'

[oh_maestro]

1.
I will call the function in the Body of the HTML document, where I want the results to show up.

The function is echo(ing) PHP code that refers back to a query that has been established already. The names all the queries are in this style: NameOfSport_Result. I want to use a variable to replace the NameOfSport part to the proper sport (VBBB, VGSwim, etc.). I reuse the same code 10 times, and I would like to have the code listed once (as a function) and use a variable to control which query is referenced.

I have an open mind, if a query has to be run "on-the-fly" I am all for that too. I'm not sure how to accomplish that though.

Are there other ways to dynamically create a query?

2.
For 1208%, what does the % stand for? Is that a way to always return today's date? I would need a dynamic way to generate the current day (w/o the year).

Thanks!

[Roger_Ramjet]

2 ways to embed a variable in a query, or rather 2 syntaxes.

//Double quoted:

$query = "SELECT * FROM table WHERE id = $id";

//Single quoted

$query = 'SELECT * FROM table WHERE id =' . $id;

I use single-quoted for all else EXCEPT my query strings. It is easier to deal with field='string' in a double quoted syntax. Elsewhere I find the single-quoted more legible.

You can build the whole query string from parts dynamically generated elsewhere.

$table = $_POST['table'];
$where = $_POST['field']."='".$_POST['value']."'";
$query = "SELECT * FROM $table WHERE $where";

[BigE]

A simple query on the fly:

Assume you have following variables posted from user input:

Sport
Gender
Age

When you build the query in a string substitute the variables:

$query = 'SELECT * FROM SPORTSTABLE WHERE Sport = "'.$POST['Sport'].'" AND Gender = "'.$POST['Gender'].'" AND Age = "'.$_POST['Age'].'"';

Then execute the query:

mysql_query($query, $yourlink);

-E

[marian]

well you said you wanted the date, but not the year; so assuming someones birthdate is together like this: 12/08/2004

all i am concern is 12/08/ that's it

so this '12/08/%' means give me everybody who is born on 12/08/ and i don't care about which year they are born; so my results will be:

12/08/2004
12/08/2003
12/08/2000
12/08/2001

i hope that is what you have in mind.

[oh_maestro]

1. My problem with the solutions to my first problem is that the queries are not based on user input. It will display the most recent score from the database for each sport/gender.
   Ideally, I could put
   <?php $Sport="Basketball";
   $Gender="Boys";
   Results($Sport, $Gender);
   ?>
   or
   <?php $Sport="Swimming";
   $Gender="Girls";
   Results($Sport, $Gender);
   ?>
   for each sport, so that the function will execute each time needed (right now it would be 10 different times on the same page).

Is there a better approach?

2. Thank you for explaining the %. My question on the date subject, how can I query the database for the current date (not including the year), not just manually typing in the date each day.

I can get the current date by doing this:
$query_Today = "SELECT * FROM 0405 WHERE DATE_SUB(CURDATE(), INTERVAL 0 DAY) = Date";

I've tried to format the date before I queried it, to no avail:
<?php $today = date("n/ j");
$query_Today = "SELECT * FROM 0405WHERE Date= $today";
?>

[Roger_Ramjet]

From the manual

"A name may start with any character that is legal in a name. In particular, a name may start with a digit; this differs from many other database systems! However, an unquoted name cannot consist only of digits."

Now I can see you are quoting the name '0405' but this is not good practice and you should change the table names. At a minimum, your design and code are not portable when using all digit table names.

The query you need is a GROUP BY:

$sql = "SELECT max(date) as d, sport, gender, result FROM '0405' GROUP BY  d, sport, gender ORDER BY d, sport, gender";

This will give you the latest results for each sport and each gender within that sport, ordered by date. It will need fine tuning depending on the actual contents of the table and what you want to see.

[oh_maestro]

I'm sorry if my posts are confusing, this is my first time to ask for help on a forum like this.

I can query the results just fine, Dreamweaver does this for me:
Example: To get Varsity Wrestling results -

mysql_select_db($database_wintersports, $wintersports);
$query_Wrestling_Result = "SELECT * FROM 0405 WHERE Level LIKE 'Varsity' AND Sport LIKE 'Wrestling' AND Gender LIKE 'Boys' AND (Result != '' OR InvTotal > 0) ORDER BY 'Date' DESC";
$Wrestling_Result = mysql_query($query_Wrestling_Result, $wintersports) or die(mysql_error());
$row_Wrestling_Result = mysql_fetch_assoc($Wrestling_Result);
$totalRows_Wrestling_Result = mysql_num_rows($Wrestling_Result);

I have queries for Results and Next games for each combination of Sport, Gender, and Level.
I then have code (listed in my first post) that will return the information from the database.

My page works fine now. I just want to streamline the code w/ a function if I can. (and learn more about PHP along the way)

2. The second problem is more of a need, I cannot get this to work for the life of me!

I was able to do this last year w/ an ASP and Access combo. I made a query in Access to match the Month / Day. My page then refernced the query in Access. Can I save a query in SQL based on current date?

[oh_maestro]

ttt