TalkPHP - Where clause issue etc

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Where clause issue etc (http://www.talkphp.com/general/2769-where-clause-issue-etc.html)

Orc	05-07-2008 09:53 AM

Where clause issue etc

Look I'm not feeling well, so here is the problem, it's saying that
my custom value is a column or something, something stupid.

sql Code:

SELECT m.mid AS mid,
    m.username AS username,
    m.email AS email 
    FROM `members` AS m 
    
    WHERE `username` = `$username`

meh whatever

by the way, $username value is in the $_COOKIE array.

returns Unknown column 'Orc-Admin' in 'where clause'

Never mind, I fixed it, yeha I forogot quotes are in it, yadda yadddda. so i suppose to have '$username'

Highway of Life

05-07-2008 05:31 PM

Anything within the $_COOKIE array can easily be spoofed and become an SQL Injection, it is still user input, so you would need to sanitise the variable before inserting it into your SQL Query.

Also, your column calls are ambiguous, you won’t need to use AS.
Example, in this query:

PHP Code:


		
			
$sql = "SELECT m.mid AS mid,
        m.username AS username,
        m.email AS email 
        FROM members AS m 
        WHERE username = '" . mysql_real_escape_string($username) . "'";

Your field names when used within mysql_fetch_assoc() are going to be:
username, email, and mid.
And they would be exactly the same if you just used the column names without the alias:

PHP Code:


		
			
$sql = "SELECT m.mid, m.username, m.email 
        FROM members AS m
        WHERE username = '" . mysql_real_escape_string($username) . "'";

They would still be: 'mid', 'username', and 'email'.

xenon

05-07-2008 09:04 PM

Extending what Highway said above, you don't need to use a table alias when you're fetching values from a single table. You're only slowing down the query. So, the following is the same with what you wrote in the first place:

PHP Code:


		
			
$sql = "SELECT mid, username, email 
        FROM members
        WHERE username = '" . mysql_real_escape_string($username) . "'";

Orc	05-08-2008 08:45 AM

theres no slow queries. lol

xenon

05-08-2008 09:39 AM

Just an advice: take the advices given to you and memorize them (or don't, I really don't care). Don't be a smart ass just for the sake of being one. Everybody here wants to help you, but they will stop doing that at one time or another, if you don't change your attitude.

Orc	05-08-2008 09:40 AM

Quote:

Originally Posted by xenon (Post 14451)

well my queries dont slow down

Highway of Life

05-08-2008 04:47 PM

Quote:

Originally Posted by Orc (Post 14452)

well my queries dont slow down

Have you run benchmarks against them? how do you know?
Running them side-by-side, you may not notice a difference, in-fact, you probably won't.
They may slow down by 0.01 seconds, which may seem insignificant, but that will make a big difference the more queries you have and the more traffic you have on your site that would cause these queries to run.
A savings of 0.01 seconds is significant on my sites. But regardless, it’s a good idea to use correct practices, and xenon is correct regarding the usage of aliases.
Aliases would be needed on multiple table queries, but are not needed when querying a single table. :)

It’s not a *big* deal, but it’s still good practice. ;)

Orc	05-08-2008 05:01 PM

Quote:

Originally Posted by Highway of Life (Post 14473)

What would you prefer then?

Highway of Life

05-08-2008 05:03 PM

Not sure I understand the question. :|

Orc	05-08-2008 05:05 PM

Quote:

Originally Posted by Highway of Life (Post 14475)

Not sure I understand the question. :|

I ment, whats the best way to grab rows from tables from the mysql? just the good old basic ways? Also, could you help me with a COUNT(i) scheme, where it has to work with Group By? I use that as an alias cause I wouldn't know what it would be otherwise when its in the array. :P

Highway of Life

05-08-2008 05:51 PM

Ah, pretty much the way you did it...

Code:

SELECT column1, column2, column3

FROM table_name WHERE column4 = 'some value';

Multiple table queries would need aliases:

Code:

SELECT a.column1, a.column2, b.field1, b.field2

FROM table_name a

LEFT JOIN another_table b

ON (a.column3 = b.field3)

WHERE column4 = 'some value';

Count queries can use an alias, but the table doesn't need an alias:

Code:

SELECT COUNT(post_id) AS total_posts

FROM posts_table

WHERE post_time > 1207677000

Your assoc array would contain $array['total_posts'];

Though I would need to see your COUNT query to understand what you’re asking. :)

Orc	05-08-2008 05:55 PM

Quote:

Originally Posted by Highway of Life (Post 14481)

Ah, pretty much the way you did it...

Code:

SELECT column1, column2, column3

FROM table_name WHERE column4 = 'some value';

Multiple table queries would need aliases:

Code:

SELECT a.column1, a.column2, b.field1, b.field2

FROM table_name a

LEFT JOIN another_table b

ON (a.column3 = b.field3)

WHERE column4 = 'some value';

Count queries can use an alias, but the table doesn't need an alias:

Code:

SELECT COUNT(post_id) AS total_posts

FROM posts_table

WHERE post_time > 1207677000

Your assoc array would contain $array['total_posts'];

Though I would need to see your COUNT query to understand what you’re asking. :)

My sql, says that

Code:

SELECT COUNT(post_id) AS total_posts

FROM posts_table

WHERE post_time > 1207677000

Gives me an error, with GROUP BY needed so yeah... :P by the way, I use the extended MySQLI php5 class library, and I use fetch_object.

Highway of Life

05-08-2008 06:15 PM

Depending on your table, and the data you are trying to obtain, you may need a GROUP BY clause, but a COUNT SELECT doesn’t require a GROUP BY in itself.
The data is key, it would depend on what kind of data you are trying to pull from your database.

All times are GMT. The time now is 03:59 AM.