php + mysql = headache (for morons like me)

[brianellisrules]

OK, let's say I have a mysql database called "brian_stickers".

And in this database, I have a table called "location".

And in this table, I have two fields, "city" and "state".

Let's say, I want to extract all the cities within a particular state.

Let's say, I also want to sort these cities in alphabetical order.

From various web-tutorials, I was able to produce this:

<?php

$db = mysql_connect("localhost", "brian_stickers");

mysql_select_db("brian_stickers",$db);

$result = mysql_query("SELECT * FROM location",$db);

while ($myrow = mysql_fetch_row($result)) {

		printf("%s", $myrow[0]);

}

?>

I tried going back and modding it with some if() statements (to use cities within a particular state) and some ksort() statements (to sort those cities), but let's face it, I'm an idiot and have no clue what I'm doing.

I was hoping someone here could lend a hand. I checked out http://www.php.net, but I'm half-retarded and no matter how hard I try, I can't understand 95% of what's written there.

 

[Thirteenva]

Maybe i'm not understanding your question, but you could pull only the cities you want by changing your sql statement.

try:

"Select * from location where state=$stateVar and city=$cityVar"


or if you wanted to pull all then only print the cities selected (not quite as efficient in my opinion) you could do this inside your 'while' loop:

if (state=$stateVar && city=$cityVar) {
   echo($myrow)
}

 

[bousozoku]

This is standard SQL but try something like this:

select * from location where state = "FL" order by city

Of course, you'll have to use whatever names you have for those fields.

 

[brianellisrules]

Thanks for the help. I gave it a shot, but now I'm having trouble debugging...

This:

<?php

$db = mysql_connect("localhost", "brian_stickers");

mysql_select_db("brian_stickers",$db);

$full_path = getenv("REQUEST_URI");

$state = basename ($full_path,".php"); 

include('/home2/brian/public_html/head.inc');

include('/home2/brian/public_html/phptest/map.inc');

$result = mysql_query("select * from location where state = $state order by city", $db);

while ($myrow = mysql_fetch_row($result)) { 

        printf("%s", $myrow[0]); 

} 

?>

produces this: http://www.brianellisrules.com/phptest/states/nj.php

Any ideas why it's not working? (note: only the nj link on the map will link to anything as of right now).



Question #2: is there a more efficient way of doing this? Right now I guess the plan is to have a .php file for every state, and just have the code for each file be the same (have it pull the name of the state from the file name, and then use that to pull the listing from the database, which is what I'm trying to do in the code above)

 

[Rower_CPU]

You don't exactly need to have a file for each state, just switch the state with variables like you're already doing (i.e. stickers.php?state=nj)

Also, make sure you've got single quotes around your vars in the SQL statement.

select * from location where state = '$state' order by city

 

[brianellisrules]

Originally posted by Rower_CPU
You don't exactly need to have a file for each state, just switch the state with variables like you're already doing (i.e. stickers.php?state=nj)

Also, make sure you've got single quotes around your vars in the SQL statement.

select * from location where state = '$state' order by city

YES!

THANKS!

Now I just need to work on the html/formatting aspect... wow, such a huge hurdle cleared (for me at least).

Thanks! Woohoo!

 

[carrington]

Question #2: is there a more efficient way of doing this? Right now I guess the plan is to have a .php file for every state, and just have the code for each file be the same (have it pull the name of the state from the file name, and then use that to pull the listing from the database, which is what I'm trying to do in the code above) [/B]

You can set a variable in your PHP page by just including it in the URL you call.

For instance, if your PHP page was called "index.php" and it used a variable called "$state" then you could call it with an URL like this:

www.brianellisrules.com/phptest/states/index.php?state=NJ

That would automatically set the $state variable to the value "NJ". That way you can have a single page (index.php or whatever you want to call it) that handles all of the states instead of 50 different pages. Much, much easier to make changes later.

Things to note if you're very new to this:

- Do not include the "$" character before the variable names in the URL (ie. use "index.php?state=NJ" instead of "index.php?$state=NJ")

- Note that the URL includes a "?" which separates the name of the file from the variable or list of variables. You only use a single "?" even if you want to set multiple variables (see next note).

- You can set multiple variables in the calling URL by separating them with an ampersand (ie. "index.php?state=FL&city=ORLANDO").

 

[brianellisrules]

Awesome, thanks for the help guys! Here's the latest version in action:

http://www.brianellisrules.com/stickers/

Now I just need to figure out a way to include pictures of the stickers that people have taken. I'm thinking of including a line in the code that's something like....

echo('<a href=http://www.brianellisrules.com/pictures/$state>Click here for pictures from $state</a>')

And then obviously setup a directory for the pictures from each state and write up an index file for each directory to show the pictures.

Unless anyone knows of a better way to automate this process (along the lines of the index.php?state=nj example).




Or... what if.... what if I named all the pictures according to state... (example: nj1.jpg, nj2.jpg... njx.jpg). Is there a way to write up a bit of code that will grab all the nj#.jpg files and display them automatically? That way, as I add more pictures, I won't have to go into each index file and change/add more code? I think this would be really slick. Perhaps some kind of if/do loop?

 

[Rower_CPU]

Something like this would work pretty well.

http://www.onlinetools.org/tools/phpimageview.php

Of course you can take what they're doing and tweak it to fit your needs.

 

[whocares]

Originally posted by ber.com
Awesome, thanks for the help guys! Here's the latest version in action:

http://www.brianellisrules.com/stickers/

Now I just need to figure out a way to include pictures of the stickers that people have taken. I'm thinking of including a line in the code that's something like....

echo('<a href=http://www.brianellisrules.com/pictures/$state>Click here for pictures from $state</a>')

Two solutions:
a. as suggested by Rower_CPU
b. take advantage of PHP/MySQL.

a. PRO = easy
CON = doesn't automatically update if photos are added

b. PRO = automatically updates
CON = more complicated!
You basically need a database with your photo file names (NJ1.jpg, NJ2.jpg or photo1.jpg etc) as well as the city. You could also add a "photos" field to your state/city database (photo1.jpg-photo2.jpg-etc). Then make a page for displaying the photos "photos.php" with a sql query like this:
"select photos from location where city='$city'"
read the list of file names and explode into an array (thus the "-" character separating the file names). This will allow you to display all the photos of a given city.

Feel free to PM me.


[edit]
also if you refer to index.php by <url>index.php?state=$state</url>
the variable $state is already there, ready to be used in index.php
[/edit]

 

[Rower_CPU]

If you have to manually add the image filenames to the DB, that's not exactly "automatic".

I thought that PHPImageView read the contents of a directory and built the page from there...?

No need to PM info that will help anyone curious about this.

 

[whocares]

Originally posted by Rower_CPU
If you have to manually add the image filenames to the DB, that's not exactly "automatic".

True I guess, but you only have to update the dbase and not regenerate all the web pages (1 for each city...). And it allows you to keep all the photos in one folder (not that you have to, though).

No need to PM info that will help anyone curious about this

I didn't want to put lenghts and lengths of code in the thread

[edit]
just read *all* the words on Rower_CPU's link. Does seem like a pretty decent - and easy, solution. Sorry for the confusion
[/edit]

 

[Rower_CPU]

Nah, since we have a whole forum at our disposal, illustrative code examples are great. No worries.

edit - J'ai noté que vous êtes de France. Quel région?

 

[carrington]

If you're interested in pulling a dynamic list of the images available in a given directory, it's very easy to do with PHP.

On another board I posted a "quick and dirty" example of how to get a directly listing, select a random image of the ones available, and then output the image with a link generated based on the image name. Some of that code, particularly the directory parsing, might be of interest to you.

The code can be found here:
http://www.theaterhopper.com/phpBB2/viewtopic.php?t=57

It's the second bit of code I posted (under the user name carrington, same as here) that includes the directory parsing routines. The post begins: "Okay, the following should handle the stuff I talked about in my previous post."

Once you grab the directory listing into an array, it would be very easy to sort or parse the array for only images that match certain criteria (only N**.gif images, etc.). Alternatively, you could only read files that match your criteria into the array.

 

[whocares]

Originally posted by Rower_CPU
edit - J'ai noté que vous êtes de France. Quel région?

De la Touraine: les châteaux et les vins de la Loire.

 

[whocares]

This thread has really started to get my brain ticking away here... So I'm thinking I could put some photos in a temp folder and then write a script to upload all content to a more definitive folder whilst adding information to a database (name, city, state, whatever you want) found in the file name (that would obviously need a strict naming scheme). Thus getting the best of both worlds: automisation and database advantages... Damn PHP is good!

 

[Rower_CPU]

Originally posted by whocares
De la Touraine: les châteaux et les vins de la Loire.

C'est pas mal.

J'espère visiter la France un de ces jours. J'ai étudié la langue beaucoup, mais je n'ai jamais visité le pays.

 

[brianellisrules]

Thanks for all the responses! I hope I can cover all my bases here...


Robin, thanks for the link. I'll check it out.


Carrington - I checked out your code earlier tonight (but I was in class, so I just skimmed it). I think it's over my head, but I'll go back and give it another look and see if I can't decipher it.


whocares - that sounds like it would work, but I'm not sure I want to include the database. seems like an extra step in the process which I could potentially eliminate.



what I envision is a directory that I can just dump all these pictures into... the pictures would be named by state, with a number... nj1.jpg, nj2.jpg, nj3.jpg, etc... as I add more pictures, I'll just add more numbers... nj4.jpg, nj5.jpg...

although now that I think about it, I'd like to be able to add a comment to each picture... maybe this is where the database comes into play... create an array of image name, state, comment/description (and any other fields). hmm... much to think about.

I guess I need to figure out how I want to do it, then start down that path. I guess the easiest would be to involve the $state variable since I'm already using that to access the database for the town names... maybe I just carry that over for the pictures too.

it's late and I'm starting to not be able to think straight. time for bed... hopefully I can find some time to tackle this tomorrow.

thanks again everyone!