Sql Queries

John_Boy

Hi people,
I have some general questions about MySQL and PHP, but really just MySQL. I'm trying to create a query, but I dont have any idea where to start. Actually, I know how to create simple query, but this one is more detailed and complex.

Table Name: Story
Table Columns: user_id, date, name, yrleft, cityfrom, countryfrom, yrcame, citycame, statecame, address, city, state, zip, AorV (audio or video), filename, instructor_approved

I need to create two queries. One based on date in descending order, and one based on countryfrom in alphabetical order. For both of these queries, the instructor_approved field has to = Y for yes, and when I return results I need it to return everything in the table EXCEPT for the name.

For god sake can someone PLEAZ! Shed some light!!!!!!

laserlight

Actually, I know how to create simple query, but this one is more detailed and complex.

Give an example of a simple query.

rincewind456

This will do the date one, don't forget the backticks around the dare field as that is a reserved word and will throw an error otherwise.

SELECT * FROM Story 
WHERE instructor_approved='Y'
ORDER BY `date` DESC

The countyrfrom one would be like this

SELECT * FROM Story 
WHERE instructor_approved='Y'
ORDER BY countryfrom ASC

John_Boy

Thanks guys. But here is another question. It's just a general one. Can you run 1 query on two different databases at the same time? Like in my Select database statement could I put db1, db2 ?

John_Boy

Thanks for the suggestions, I almost have it but this is what I've got so far, and it still isn't EXACTLY what I want

[code=php]$query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";

The file name I need to query from the most recent date and I dont think '' is what I want. Any suggestions????

John_Boy

What everyone has said has helped me out, and I've made progress on my queries, however they still are not what I need and I'm not sure how to fix it. So here goes. I have a table that has
UserID, date, name, yrleft, cityorigin, countryorigin, yrarrival, cityarrival, starrival, email, address, city, state, zip, aorv, filename, and approved

Mandatory information includes the date, yrleft, cityorigin, countryorigin, yrarrival, cityarrival, starrival, and aorv. The filename is named with a timestamp as to when they left their recording or comment.

I need to run a query by dates from (1850-1950) so whatever date they put in, as long as it is within that time period, that it will return the results. I want to display the earliest date first. I want the results to return all the mandatory information. And approved must = Y

This is what I've gotten so far

$query = $query = "SELECT Userid, date, yrleft, cityorigin, countryorigin, yrarrival, 
cityarrival, statearrival, filename FROM Story WHERE approved = 'Y' AND ORDER by date ASC

Could someone tell me why this still isn't working? I need

Searches ==== sort based on search type: Dates - descending
returns....all required fields except name...include Id and filename

khendar

So are you trying to get the rows matching a particular date as entered by the user ?

 $query = $query = "SELECT Userid, date, yrleft, cityorigin, countryorigin, yrarrival,
cityarrival, statearrival, filename FROM Story WHERE approved = 'Y' AND date = $date ORDER by date ASC

Like that ?

John_Boy

I'm getting a parse error on the last line of my code at the /html

[code=php]<html>

<head>
	<title>Eye Contact - Century to Century</title>
	<link rel="stylesheet" href="eye01.css" type="text/css" />
</head>

<?php
$db = mysql_connect("localhost","root","");
if (!$db){
		print "error - Could not connect to MySQL";
		exit;
}

$er = mysql_select_db("touch_art1");
if(!$er){
	print "error - Could not select the database";
		exit;	
}

$query = $query = "SELECT yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename FROM story WHERE pamapproved = 'Y' and city_origin = $city_origin ORDER by city_origin ASC;
$result = mysql_query($query);

//check to see if query worked
if (!$result)
{
	echo 'Query on Connection failed!';
	// exit	
}
else
{
	echo 'succees';
	// process results

while($row = mysql_fetch_row($result)) {
$date = $row[1];
echo '$date'; 
$name =$row[2];
echo'$name';

}
}
?>

</html>

Anyone know why?

khendar

Yep. You didn't close the $query string. All of that red code should be a dead giveaway.

John_Boy

Got it! Thanks a bunch