Query Check Please

Databases
1

Hi Guys

Below is a Database ER Diagram for my project.

Here is the Query that I am trying to run:

SELECT jokeid, ipaddress FROM ratings
					WHERE
					(ratings.jokeid == jokes.jokeid) && (ipaddress == $_SERVER['REMOTE_ADDR'])

However I’m getting an error, and I can’t figure out why :frowning:

What I’m trying to do is get the ID of the current joke and get the IP Address of the Visitor and then look them up in the ratings table.

I would greatly appreciate some help in getting this working.

Thank You.

2

  1. I think && should be AND

  2. I don’t think you need to join the 2 tables

what is the error message?

I think you’ll have a syntax error in your evaluated query

3

actually, i doesn’t have to be (&& works fine), but it ~should~ be AND, because AND is better

sort of like how not using “tbl” and “fld” prefixes is better

:slight_smile:

actually, there is no join in that query, but there is an undefined table reference, nicely spotted

:slight_smile:

and there is one more error you overlooked

:slight_smile:

4

neither can we, if you don’t tell us what it is

(actually, i know what’s wrong, but i want you to get used to the idea of offering useful information, such as the actual error message, that will help people help you)

:slight_smile:

5

Hello again Kalon :smiley: Good to see you again, I hope you’re well

I noticed you have the code in two separate code blocks. Does that mean I run 2 queries ?

Or do I do this:

SELECT jokeid, ipaddress FROM ratings 
                    WHERE 
                    ipaddress == $_SERVER['REMOTE_ADDR']

Thanks

6

that’s why I said “should” and not “must” :wink:

I didn’t say there were no more errors :wink:

7

You were too quick. Those 2 code blocks were a result of copy and paste not working correctly from your original code and so I deleted it from my post since there are other problems in your code anyway.

To help debug your code try

$query = "SELECT jokeid, ipaddress FROM ratings
WHERE
ipaddress == $_SERVER[‘REMOTE_ADDR’]";

echo $query; die();

and then post the reult. I think you’ll then see at least a syntax error.

But what was the error message(s) you got?

8

Hi Kalon

Here is the Error Message:

Parse error: syntax error, unexpected T_STRING in play.php line 17

By the way I used the following:

SELECT jokeid, ipaddress FROM ratings  
                    WHERE  
                    ipaddress == $_SERVER['REMOTE_ADDR']
9

ok, now what is the evaluated query actually being run when the value of $_SERVER[‘REMOTE_ADDR’] is put into the query?

(see my previous post)

10

MySQL uses = for comparison, not ==

I sometimes think they did that just to mess with us … :wink:

11

party pooper :frowning:

i wanted to see how long it would take kalon to find that

:slight_smile:

12

there’s still another error, by the way

:slight_smile:

13

debugging hint

display the evaluated query in your browser and then copy and paste into an sql window from SQLyog (which is what I use) or phpMyAdmin etc and run it.

I find it easier to get sql statements working in an sql window first (for complicated queries) and then transfer them to php code.

Another hint: is ipadress a string in your database table?

14

I saw it in post 1 but like you didn’t want to “spill the beans” all in 1 go.

It’s better to guide the OP through the debugging process after which hopefully they will learm more.

15

:eek: sorry 'bout that

runs and hides

16

OK Here is where I’m at right now.

SELECT jokeid, ipaddress FROM ratings  
                    WHERE  
                    ipaddress = $_SERVER['REMOTE_ADDR']

@Scallio: Thanks for that. :slight_smile:

@r937: You’re enjoying watching me suffer with this aren’t you :lol:

@Kalon: Thank You for you final hint. I did not really get the Debugging Hint though !

OK if I understand the hint correctly It’ll be something like so:

$_SERVER[‘REMOTE_ADDR’] return an Integer ? Yet my ipaddress type is a VarChar(16)

Do I some how have to make the datatypes compatible ?

17

By the way guys why is it so hard to understand stuff from the PHP Manual ?

http://www.php.net/manual/en/reserved.variables.server.php

Under Remote_Addr it doesn’t say what the return type is !! How am I suppose to find out ? :frowning:

18

Well it says it will return the IP of the visitor.
Since IP’s contain multiple dots (or colons for IPv6), it certainly won’t return any numeric type. Arrays and objects are also out of the question because IP’s are scalar values, not compound.
The leaves just one possibility doesn’t it? A string :cool:

BTW Mind you that IPv6 addresses are longer than IPv4 addresses and a VARCHAR(16) won’t cut it for IPv6 addresses.

How long can a IPv6 address be you ask? :google:

:slight_smile:

19

sorry if it comes across that way

we’re trying to teach you the procedure for how to solve problems

you know the old saying, give a man a fish and you’ll feed him for a day, teach a man to fish and he’ll sit in a boat all day drinking beer

:smiley:

20

If the column datatype is a string then the value for that column in a select query should be in quotes.

try

$query = 'SELECT jokeid, ipaddress FROM ratings
WHERE
ipaddress = "'.$_SERVER[‘REMOTE_ADDR’].‘"’;

and then directly below that line add

echo $query; die();

** this will display in your browser the actual query being run and it should work

note: the colours are due to the fact I can’t get the code tags to work after I copy and paste your code from your post to mine :frowning: