Where clause issue etc

Orc · 05-07-2008, 09:53 AM

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

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.

well my queries dont slow down

Highway of Life · 05-08-2008, 04:47 PM

Quote:

Originally Posted by Orc

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

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. ;)

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

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

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.