second problem with date modification.

[tommr]

 

I am trying to get this script to display the date like Dec 17, 2010 and have the search function work too.

If I try to change the date the search malfunctions and if I take the date function away the date is 2010 12 17

 

I thought we had this nailed down the other day but in my excitement I neglected to test the city search.

Here is the script.

<?php

 

$find  = trim($_GET['find']);

$field = $_GET['field'];

 

if($find && $field) { // we have search form submitted

// check for values to prevent sql injection

$valid_fields = array("venue_state", "venue_city", "start_date");

if(!in_array($field, $valid_fields)) die("Error: Invalid field!");

 

//connect

mysql_connect("localhost", "arts_cshow", "TrPh123Yuo") or die(mysql_error());

mysql_select_db("arts_shows") or die(mysql_error());

 

echo "<h2>Search Results for $find in $field</h2>\n";

 

$find = addslashes($find);

$result = mysql_query("SELECT * FROM craft_shows WHERE $field LIKE '%$find%'");

if(mysql_num_rows($result) == 0) {

echo "<p>0 matches found.</p>";

}

else {

echo "<table><tr><td class=\"shows\">";

 

$sql = "SELECT *, DATE_FORMAT(`start_date`, '%b %e, %Y') AS s_date FROM craft_shows";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result))

 

{

echo "<a href='/show_submits/show_detail.php?id={$row['id']}'>Details</a>\n";

echo $row['venue_state'] . "       {$row['s_date']}         {$row['show_name']}       {$row['venue_city']}<br>\n";

}

echo "</td></tr></table>\n";

}

}

?>

 

 

thanks

 

[denno020]

You should do a search on Google for how to pull the data from the system, and then brake it up into variables (like hours, minutes, seconds etc.).

 

Then you can display the time in whatever format you want.

 

Denno

[tommr]

This script breaks the date down fine, the trouble is it interferes with the search function.

I was hoping that someone could spot the conflicting code.

 

Not to mention that a large percentage of what I have found posted on the internet is incorrect in one way or another.

[PFMaBiSmAd]

You post doesn't exactly demonstrate what problem you are having. What value are you entering, what result are you getting, and what result do you expect to get?

[tommr]

I am sorry if I was vague.

I was hoping there was a obvious error in my script that would be obvious to someone who knows more about PHP than I do. 

This script searches for info and displays it in a fashion that includes city, state, show name, date etc.

There is a test version at  http://www.artsandcraftsnetwork.com/shows

if you search for ny in state or watertown in city you will get the same results.

I want to break the date down so people can understand it instead of the date format that is stored in the database.

This code breaks the date down but something in the code below is conflicting with the search function of the script.

Here is the part code that changes the date display.

If I remove this from the code the search works fine but the date is messed up.

      $sql = "SELECT *, DATE_FORMAT(`start_date`, '%b %e, %Y') AS s_date FROM craft_shows";

$result = mysql_query($sql);

      while($row = mysql_fetch_array($result))

 

The problem I am having is this code interferes with the search function of the script and no matter which search term is selected , state, city or date, all the results are returned.

 

Here is the form that goes with the script.

<form name="search" method="get" action="<?php $PHP_SELF?>">

Seach for: <input type="text" name="find" /> in <Select NAME="field">

<Option VALUE="venue_state">state</option>

<Option VALUE="venue_city">city</option>

<Option VALUE="start_date">date</option> </Select>

<input type="hidden" name="searching" value="yes" />

<input type="submit"  value="Search" />

</form>

 

[PFMaBiSmAd]

When prompting a visitor for a date, you cannot let them enter it free form as you will get a dozen different formats. You must use three drop down select menus, one each for the day, month, and the year and/or you need to use a date-picker that allows them to click on a date on a calendar to pick a date.

 

I'm going to guess that the answer to my question - What value are you entering?, is that you are entering a date in some format only known to you and you are putting that into the WHERE $field LIKE '%$find%'" search and you are expecting that to find the matching rows, but it does not and instead executes the second query in your code that displays everything?

[tommr]

I am sorry that I can not explain this in a fashion that anyone can understand.

 

Forget about the "search by date".  When users enter a date they must use the calendar I provide in the submit form so that the date is input in the proper fashion.  That is not the problem.

 

If you search by anything the date will be in the results as well as show name, address and so on.

 

The problem is the script will print the date as 2010 17 12 and so on.

The code I submitted will change the date from 2010 17 12 to Dec 17 2010 when I use the solution to fix the date it interferes with all the search so if you search for watertown in city it returns all the results in the table instead of only the watertown results.

 

If I take the date fix code out the search will return the proper results, by city or state,  but the date is displayed as 2010 17 12 which is too confusing for users to understand.

 

I was hoping that someone might spot a coding error or conflict that was causing the search to return all results instead of the state or city.

 

 

 

[PFMaBiSmAd]

Only one of your two queries use DATE_FORMAT() in it to return the stored 2010 17 12 date in the Dec 17 2010 format that you want. You would probably want to use DATE_FORMAT() in both queries.

[litebearer]

An aside - the date format one displays and the date format one uses in the script to perform various actions CAN be two different format - the format used to display to a user has NO effect on the your search/calculations

[PFMaBiSmAd]

After looking more closely at the LOGIC in your code, it is no wonder it does not do what you expect. You are executing one query with a WHERE clause in it to find if there are any results, then executing a different query on that same table to get the results and display them.

 

Just simplify and fix the logic in your code so that it executes one query.

[PFMaBiSmAd]

You would need to use the following logic -

	$result = mysql_query("SELECT *, DATE_FORMAT(`start_date`, '%b %e, %Y') AS s_date FROM craft_shows WHERE $field LIKE '%$find%'");
if(mysql_num_rows($result) == 0) {
	echo "<p>0 matches found.</p>";
} else {
	echo "<table><tr><td class=\"shows\">";
	while($row = mysql_fetch_array($result)) {
		echo "<a href='/show_submits/show_detail.php?id={$row['id']}'>Details</a>\n";
		echo $row['venue_state'] . "       {$row['s_date']}         {$row['show_name']}       {$row['venue_city']}<br>\n";
	}
	echo "</td></tr></table>\n";
}

[tommr]

Thank you PFMaBiSmAd very much.

This is working fine now.

I tried to condense the code for the query but I did not have section grouped properly.

I'm not convinced that I can learn this from a book.

 

And thanks to all those who contributed and helped with this problem especially kenrbnsn and Pikachu2000.

 

This thing is almost ready, just 2 more flaming hoops!