Old 15th November 2003, 14:10   #1
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
Need MySQL & PHP Help

I'm learning MySQL along with PHP and I'm making a download script now. It's for downloading two TV shows, "Fifth Gear" and "Top Gear" from different dates and it also keep track of the hits.

So, I have a table with:

code:
id (primary) | show | episode | url | hits
---------------------------------------------------
int | enum - | date | text | int
auto_increment | 'fifth' | | |
| or 'top' | | |

Then you go to like download.php?show=fifthgear&date=2003.11.15 and I have it checking for isset, etc.

Anyway, I would like to know how I get my script to (it's already connected to the database) if $show = fifthgear and $date = blah, find the URL that goes with the 'show' of 'fifth' and the 'episode' of 'blah' and tell me if that one doesn't exsist in the database.

Follow?

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 15th November 2003, 14:54   #2
fwgx
Rudolf the Red.
(Forum King)
 
fwgx's Avatar
 
Join Date: Nov 2000
Posts: 9,314
$query = "SELECT url FROM DB_TABLE WHERE show='fifth' AND episode='date'";
$result = mysql_query($query);
$num = mysql_affected_rows();
if($num == 1) {
$result_row = mysql_fetch_row($result);

echo $result_row[0];
}

.: fwgx.co.uk.:.My art:.

"We think science is interesting and if you disagree, you can fuck off."
fwgx is offline   Reply With Quote
Old 15th November 2003, 15:16   #3
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
Hmm, the if $num = 1 test is failing...

http://test.viper007bond.com/downloa...ate=2003-11-12

PHP Code:
if (isset($show) && isset($date)) {
    if (
$show == "fifthgear") {
        echo 
"You picked the show Fifth Gear and the episode from ".$date.".<br />";

        
$query "SELECT url FROM $table_name WHERE show='fifth' AND episode='".$date."'"
        
$result mysql_query($query); 
        
$num mysql_affected_rows(); 
        if(
$num == 1) { 
            
$result_row mysql_fetch_row($result); 
            echo 
$result_row[0]; 
        }
        else {
            echo 
"You sir, are a moron.";
        }
    }
    elseif (
$show == "topgear") {
        echo 
"You picked the show Top Gear and the episode from ".$date.".<br />";
    }
    else {
        echo 
"You picked an invalid show.<br />";
        echo 
"<br />";
        echo 
"<a href=\"http://5thgear.viper007bond.com/\">Return to the site.</a>";
    }

Quote:
You picked the show Fifth Gear and the episode from 2003-11-12.
You sir, are a moron.
This is a line from the table:

code:
id: 1
show: fifth
date: 2003-11-12
url: Blah...
hits: 0

I'll look up those PHP commands and debug later. It's time for breakfast.

- Viper007Bond | Viper007Bond.com

Last edited by Viper007Bond; 15th November 2003 at 15:31.
Viper007Bond is offline   Reply With Quote
Old 15th November 2003, 15:47   #4
anubis2003
Forum King
 
anubis2003's Avatar
 
Join Date: Dec 2002
Location: middle of somewhere
Posts: 5,564
Send a message via AIM to anubis2003
try $num > 0 - you may have it in the table twice...
anubis2003 is offline   Reply With Quote
Old 15th November 2003, 15:51   #5
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
I don't believe I do, but I'll try that.

Nope, that wasn't it.

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 15th November 2003, 15:52   #6
fwgx
Rudolf the Red.
(Forum King)
 
fwgx's Avatar
 
Join Date: Nov 2000
Posts: 9,314
Make the show colum a text field, you're trying to compare a string to a number in the SELECT because the actual value in show is a number. At least it appears to be as it's an enum. Also keep things consistant your select uses the $date var whereas the show uses an explicit string. You want to be able to use something like this:

"SELECT url FROM $table_name WHERE show='".$show."' AND episode='".$date."'";

If num always returns 0 then it's not selecting anything at all, check your SQL command and if that fails try using '*' instead of 'url'.

.: fwgx.co.uk.:.My art:.

"We think science is interesting and if you disagree, you can fuck off."
fwgx is offline   Reply With Quote
Old 15th November 2003, 15:53   #7
anubis2003
Forum King
 
anubis2003's Avatar
 
Join Date: Dec 2002
Location: middle of somewhere
Posts: 5,564
Send a message via AIM to anubis2003
that's right - I missed that - I just figured that episode was a string.
anubis2003 is offline   Reply With Quote
Old 15th November 2003, 15:56   #8
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
Alright, thanks guys. I'll mess with it after breakfast. It sounds like it'll work now.

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 15th November 2003, 17:12   #9
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
Debugging now, but I think I'll get it.

- Viper007Bond | Viper007Bond.com

Last edited by Viper007Bond; 15th November 2003 at 17:45.
Viper007Bond is offline   Reply With Quote
Old 16th November 2003, 16:33   #10
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
PHP Code:
$query mysql_query("SELECT * FROM $table_name WHERE show='fifth' AND date='$date'");
$result mysql_fetch_array($query);
echo 
$result[url]; 
Error:
Quote:
1064: You have an error in your SQL syntax near 'show='fifth' AND date='2003-11-16'' at line 1
I want to find the line where show='fifth' and date='2003-11-16' and then output the value for url on that line.

What's the correct query format?

(BTW - I renamed the "episode" collumn to "date".)

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 17th November 2003, 07:47   #11
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 17th November 2003, 08:48   #12
fwgx
Rudolf the Red.
(Forum King)
 
fwgx's Avatar
 
Join Date: Nov 2000
Posts: 9,314
http://www.mysql.com/doc/en/Using_DATE.html
http://www.mysql.com/doc/en/Date_calculations.html

.: fwgx.co.uk.:.My art:.

"We think science is interesting and if you disagree, you can fuck off."
fwgx is offline   Reply With Quote
Old 17th November 2003, 09:01   #13
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
Nah, the date collumn is just text, so it's just doing a text to text comparison.

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 17th November 2003, 13:34   #14
fwgx
Rudolf the Red.
(Forum King)
 
fwgx's Avatar
 
Join Date: Nov 2000
Posts: 9,314
Try using phpMyAdmin to create the query and see what query string it comes up with.

edit: try putting back slashes before the single quotes.

.: fwgx.co.uk.:.My art:.

"We think science is interesting and if you disagree, you can fuck off."
fwgx is offline   Reply With Quote
Old 18th November 2003, 05:49   #15
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
http://lightning.phpfreaks.com/forums/topic10728.php

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 18th November 2003, 06:17   #16
anubis2003
Forum King
 
anubis2003's Avatar
 
Join Date: Dec 2002
Location: middle of somewhere
Posts: 5,564
Send a message via AIM to anubis2003
don't use text - use varchar instead. That's what they are getting at.
anubis2003 is offline   Reply With Quote
Old 18th November 2003, 10:19   #17
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
/me tries

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Old 18th November 2003, 15:03   #18
Viper007Bond
Major Dude
 
Viper007Bond's Avatar
 
Join Date: May 2002
Location: Portland, OR Life: None
Posts: 1,054
Send a message via ICQ to Viper007Bond Send a message via AIM to Viper007Bond Send a message via Yahoo to Viper007Bond
"show" and potentially "date" are reserved names. I changed 'em and it works. >_<

- Viper007Bond | Viper007Bond.com
Viper007Bond is offline   Reply With Quote
Reply
Go Back   Winamp & Shoutcast Forums > Community Center > General Discussions

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump