php/mysql issue, any advice?

[dreams4000]

Hi all,

I have basically a simple one page display of some information. It is an event chart. some of these events span multiple days so they have a start and end date and some are only one day so basically have just a start date. The issue is that all the information needed to display is in three different mysql tables. (has to stay this way as im displaying information from another program that uses these)

The events table has the start date, description and some other info. (each event has an id number)

The repeating table is used for events that span more than one day and have the second date I mentioned. (also associated with the same event id)

The last table has some other description info that has to show as well. (also has an associated event id)

I can get things to show fine if I am doing just two tables, but the issue comes up when I want the third tables info to show as well.

I can do something like:

$query = "SELECT DATE_FORMAT(webcal_entry.cal_date, '%d %M %Y'),webcal_entry.cal_create_by,webcal_site_extras.cal_data,webcal_entry.cal_description FROM webcal_entry,webcal_site_extras WHERE webcal_entry.cal_id = webcal_site_extras.cal_id ORDER BY webcal_entry.cal_date DESC";

And this displays info from the events table and the extra description info table just fine. If I use an AND command to append the third table's webcal_entry_repeats.cal_id = webcal_entry.cal_id then it only displays the items that are in just the repeats table and not the others. If I use an OR command it shows tons of junk. I am a bit lost what I need to do to get the third table's info to show with just the ones that have the end date. Any help is appreciated. Thanks!

[dreams4000]

no ideas anyone? Just trying to get these three tables to play nice:\

[vallette]

Use a LEFT OUTER JOIN. This will get info from the 3rd table if it exists and ignore it if it doesn't:

SELECT DATE_FORMAT(webcal_entry.cal_date, '%d %M %Y'),webcal_entry.cal_create_by,webcal_site_extras.cal_data,webcal_entry.cal_description
FROM webcal_entry
INNER JOIN webcal_site_extras ON webcal_site_extras.cal_id=webcal_entry.cal_id
LEFT OUTER JOIN webcal_repeat ON webcal_repeat.cal_id=webcal_entry.cal_id
WHERE 1=1
ORDER BY webcal_entry.cal_date DESC

Not sure what the name of the 3rd table is and what criteria you want in the WHERE clause but this should be a start.

[classicstyle]

1 more question - I'm helping Dreams4000 on this project. I am trying to be able to only query ones that are in any given month.

I have created a select list, each option represents a month, with the value of 200401, 200402, etc. Then I set the form action to:

echo "<table border=\"0\"  width=\"180\" bgcolor=\"#FFFFFF\">";
				echo "<tr>";
  				printf ("<form method=\"post\" action=newtest2.php?filter=%s>",$filter);

			echo "<td><SELECT NAME=\"filter\">";
			echo "<option value=\"200401**\">January</option>";
			echo "<option value=\"200402**\">February</option>";
			echo "<option value=\"200403**\">March</option>";
			echo "<option value=\"200404**\">April</option>";
			echo "<option value=\"200405**\">May</option>";
			echo "<option value=\"200406**\">June</option>";
			echo "<option value=\"200407**\">July</option>";
			echo "<option value=\"200408**\">August</option>";
			echo "<option value=\"200409**\">September</option>";
			echo "<option value=\"200410**\">October</option>";
			echo "<option value=\"200411**\">November</option>";
			echo "<option value=\"200412**\">December</option>";
			echo "</select>";
			echo "<td><input type=\"Submit\" name=\"submit\" value=\"OK\">";
			echo "</font>";
			echo "</form>";
	echo "</table>";

datefilter is the name assigned to the select list.

The first problem is, when I hit submit, it doesn't put the value for $datefilter in the url.

The second part is, will mySQL see the 200401** as wildcards, so that I can do something like:

$query = "SELECT various fields FROM table WHERE column_date = '" . $filterdate . "'";

to make the query only get rows where the date matches the filter variable?

[dreams4000]

Thanks Vallette your advice worked perfect! Really appreciate it. If you have any last ideas on Classic's post, it would be great. Thanks again for your help!

[vallette]

A couple of things:
First, when you mention 'datefilter' I assume you mean 'filter'. There was no 'datefilter' variable in your code.
Second, when you 'post' a form none of your form parameters will show up in the URL. If you want them to, use 'method="get"'

I cleaned up your code a little bit and changed the month value to a digit:

<form method="post" action=newtest2.php?filter=<?php echo $datefilter ?>>
<table border="0"__width="180" bgcolor="#FFFFFF"> 
	<tr>
		<td>
			<SELECT NAME="datefilter"> 
				<option value="1">January</option> 
				<option value="2">February</option> 
				<option value="3">March</option> 
				<option value="4">April</option> 
				<option value="5">May</option> 
				<option value="6">June</option> 
				<option value="7">July</option> 
				<option value="8">August</option> 
				<option value="9">September</option> 
				<option value="10">October</option> 
				<option value="11">November</option> 
				<option value="12">December</option> 
			</select>
			<p>
				<input type="Submit" name="submit" value="OK"> 
			</p>
		<td>
	</tr>
</table>
</form> 

Now for the select statement just do:

$query = "SELECT various fields FROM table WHERE MONTH(column_date) = '".$datefilter."'";

The MONTH() function returns the month number from a date.

One more thing, the wild card in SQL is '%' and it's used with 'LIKE' or 'NOT LIKE'. The query:
SELECT * FROM blah where name LIKE 'jo%'

would find Joe, jon, john, Joanne, etc.

[dreams4000]

This seems to be somewhat working. Anytime Feb is selected it displays the items for Feb, but no other month.

Below is the whole file short of the includes and mysql connect info, it has your new items in it. Maybe you can see something I am lost on. I don't see why Feb items work but no other dates. Dates are being stored in the database under the cal_date column as 20040225 The feb stuff seems to be correctly reading from the 02 part as if I change it to anything else, those items dissapear. (please forgive the switching between echos and html, this code at the moment is a mismash of stuff, just want to get it working before that kind of clean up)

<?php
if (!empty($datefilter)){

$query = "SELECT DATE_FORMAT(webcal_entry.cal_date, '%d %M %Y'),webcal_entry.cal_create_by,webcal_site_extras.cal_data,webcal_entry.cal_description, DATE_FORMAT(webcal_entry_repeats.cal_end, '%d %M %Y') FROM webcal_entry INNER JOIN webcal_site_extras ON webcal_site_extras.cal_id=webcal_entry.cal_id LEFT OUTER JOIN webcal_entry_repeats ON webcal_entry_repeats.cal_id=webcal_entry.cal_id  WHERE MONTH(webcal_entry.cal_date) = '".$datefilter."'";

}
$result = mysql_query ($query);
if (empty($id)){  // Checks the url to see if there is a value for $id.

$row = mysql_fetch_assoc ($result);

echo "<table border=3 cellpadding=\"1\" bgcolor=\"#ffffff\" width=\"100%\">";
echo "<tr><td><center><img src=\"logo.jpg\"></center><td><center><img src=\"completeresdata.gif\"></center></tr></td></table>";				                                

?>			
 <form method="get" action=newtest3.php?filter=<?php echo $datefilter ?>> 
<table border="0"__width="180" bgcolor="#FFFFFF"> 
    <tr> 
        <td> 
            <SELECT NAME="datefilter"> 
                <option value="1">January</option> 
                <option value="2">February</option> 
                <option value="3">March</option> 
                <option value="4">April</option> 
                <option value="5">May</option> 
                <option value="6">June</option> 
                <option value="7">July</option> 
                <option value="8">August</option> 
                <option value="9">September</option> 
                <option value="10">October</option> 
                <option value="11">November</option> 
                <option value="12">December</option> 
            </select> 
            <p> 
                <input type="Submit" name="submit" value="OK"> 
            </p> 
        <td> 
    </tr> 
</table> 
</form>  			 
   <? 

			                              echo "</table>";

echo "<table border=2 cellpadding=\"1\" cellspacing=\"0\" bgcolor=\"#99CCFF\" width=\"100%\"><tr bgcolor=\"#99CCFF\" align=\"center\">\n";
echo "<tr><td width=10%><B>Date Starting</b></td><td><B>User</b></td><td><B>Servers Reserved</b></td><td><B>Description</b></td><td><B>Date Ending</b></td></tr>";


	                          $i = 0; // sets up the counter for alternating table row colors.

	                          while ($row = mysql_fetch_assoc ($result)) {
		                            echo "<TR>\n";
		                              foreach ($row as $column) {//My alternating colors
		                              			if($i % 2 == 0) //checks to see if the line is an even number
															{
															echo "<td valign=\"center\" bgcolor=\"#EEF7ff\">$column</TD>\n";

															}else{

															// The whole purpose of this second area is to just change the highlighting of the table

															echo "<td valign=\"center\" bgcolor=\"#DBEDff\">$column</TD>\n";
														}//End Alternating colors
			                                   //echo "<TD>$column</TD>\n";  // Original display
			                                   }
			                                    echo "</TR>\n";
			                                    $i++;
			                                    }		                                  
			                              echo "</table>";
}

[dreams4000]

Arg.. ok.. I figured it out.. was missing one bit of code

mysql_data_seek ($result, 0);

popped that in and it works for months🙂

One last little question, if I wanted to add in year sorting, so that instead of just Jan or Feb. I could have Jan 04 or Feb 05 etc.. What would I need to place for that? Thanks so much!