vb.org Archive - what's wrong with this query?

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - what's wrong with this query? (https://vborg.vbsupport.ru/showthread.php?t=91967)

sabret00the

07-11-2005 09:08 AM

what's wrong with this query?

even when i use the echo to read it via the top of the page it stops at the table prefix.

[sql] SELECT username, userid, email
FROM prs_users
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid = prs_users.userid)
WHERE prs_users.subscriptions LIKE = "% $display[pieceid] %" AND userid != $getparentinfo[userid]
[/sql]

and i'm calling it like this

PHP Code:


		
			
        $thesubscribed = $DB_site->query("

            SELECT username, userid, email

            FROM prs_users

            LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid = prs_user.userid)

            WHERE prs_users.subscriptions LIKE = "% $display[pieceid] %" AND userid != $getparentinfo[userid]

        ");

Colin F

07-11-2005 12:16 PM

Could you post the exact error message?

Also, could it be that prs_users also needs a TABLE_PREFIX?

sabret00the

07-11-2005 12:26 PM

here you go

PHP Code:


		
			
Database error in vBulletin 3.0.7:



Invalid SQL: 

            SELECT username, userid, email

            FROM prs_users

            LEFT JOIN 

mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3



mysql error number: 1064

and nope prs_users has no table prefix what so ever :(

The Geek

07-11-2005 12:35 PM

Tough to say, however it would at least read a little better and be easier to troubleshoot written like this:

PHP Code:


		
			
$thesubscribed = $DB_site->query(" 
            SELECT p.username, p.userid, p.email 
            FROM prs_users p 
            LEFT JOIN " . TABLE_PREFIX . "user u ON p.userid = u.userid 
            WHERE p.subscriptions LIKE '%$display[pieceid]%' AND u.userid != $getparentinfo[userid] 
        ");

I dont know if thats right - nor do I know if I got the fields on the right aliases (p for prd_users and u for users).
Hell, this may actually make it harder for you to troubleshoot - I just thought I would trow it in as its the style I would use.

I point to note is that "% $display[pieceid] %" would have given you problems for a number of reasons (in fact, it shouldnt have compiled).

HTH's m8

sabret00the

07-11-2005 12:40 PM

funnily enough i just changed it to read a bit better with this

PHP Code:


		
			
        $thesubscribed = $DB_site->query("

            SELECT user.username, prs_users.userid, user.email

            FROM prs_users

            LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid = prs_users.userid)

            WHERE prs_users.subscriptions LIKE = "% $display[pieceid] %" AND prs_users.userid != $getparentinfo[userid]

        ");

and i know all the colums are there so it's just confusing me.

Quote:

Originally Posted by The Geek

I point to note is that "% $display[pieceid] %" would have given you problems for a number of reasons (in fact, it shouldnt have compiled).

you're right the single quotes fixed it, thank you :)

though now i'm getting

Code:

Database error in vBulletin 3.0.7:



Invalid SQL: 

                        SELECT user.username, prs_users.userid, user.email

                        FROM prs_users

                        LEFT JOIN user AS user ON (user.userid = prs_users.userid)

                        WHERE prs_users.subscriptions LIKE = '% 1 %' AND prs_users.userid != 0

                

mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '% 1 %' AND prs_users.userid != 0' at line 4



mysql error number: 1064

not sure what that means :(

nevermind the problem was the = <--- thanks again :)

akanevsky

07-11-2005 01:09 PM

The best and most valid way to write this query would be:

[SQL]$thesubscribed = $DB_site->query
("
SELECT p.username, p.userid, p.email
FROM prs_users p
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid = p.userid)
WHERE p.subscriptions LIKE '%" . $display[pieceid] . "%' AND p.userid != '" . $getparentinfo[userid] . "'
");[/SQL]

Colin F

07-11-2005 01:42 PM

Quote:

Originally Posted by Dark Visor

I like to have "FROM prs_users AS p", but it's not as if it would make a difference.

Marco van Herwaarden

07-11-2005 01:44 PM

Quote:

Originally Posted by Dark Visor

The best and most valid way to write this query would be: [SQL]$thesubscribed = $DB_site->query("SELECT p.username, p.userid, p.emailFROM prs_users pLEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid = p.userid)WHERE p.subscriptions LIKE '%" . $display[pieceid] . "%' AND p.userid != '" . $getparentinfo[userid] . "'");[/SQL]

Most valid would be using also $display['pieceid'] instead of $display[pieceid]. And depending on the column type and where that value is coming from, maybe also measurements to avoid injections, and amybe more but that is difficult to tell based on the info we have. ;)

ohh and yes like mentioned before, TABLE_PREFIX on all tables.

All times are GMT. The time now is 08:16 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01130 seconds Memory Usage 1,750KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (4)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (8)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: