CodeIgniter Forums
[SOLVED] query with variables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: [SOLVED] query with variables (/showthread.php?tid=42579)

[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]predat0r[/eluser]
Hi, i get a 1064 parse error at the last line. I found and tried several method but not find the right solution. Maybe the parameters has different types?

Code:
function get_articles($year, $month)
{
$query = $this->db->query("SELECT id, title, preview, SUBSTRING('date', 1, 4) as year, SUBSTRING('date', 6, 2) as month, seo_title
FROM articles
WHERE year ='".$year."' AND
WHERE month ='".$month."'");    
...
}

Error Number: 1064

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 'WHERE honap ='05'' at line 4

SELECT id, title, preview, SUBSTRING('date', 1, 4) as year, SUBSTRING('date', 6, 2) as month, seo_title FROM articles WHERE year ='2011' AND WHERE month ='05'

Filename: C:\wamp\www\mezobereny\system\database\DB_driver.php

Line Number: 330

[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]Seb[/eluser]
You should remove the second "WHERE":

Code:
SELECT id, title, preview, SUBSTRING(‘date’, 1, 4) as year, SUBSTRING(‘date’, 6, 2) as month, seo_title FROM articles WHERE year =‘2011’ AND month =‘05’


[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]John_Betong_002[/eluser]
Try this:

Code:
SELECT
   id,
   title,
   preview,
   SUBSTRING(‘date’, 1, 4) as year,
   SUBSTRING(‘date’, 6, 2) as month,
   seo_title
FROM
   articles
WHERE (year =‘2011’ AND month =‘05’)
 

[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]predat0r[/eluser]
Thanks, now other error comes (with the modifications done):

Error Number: 1054

Unknown column 'year' in 'where clause'

SELECT id, title, preview, SUBSTRING('date', 1, 4) as year, SUBSTRING('date', 6, 2) as month, seo_title
FROM articles
WHERE year = '2011' AND
month = '05'

EDIT: as i read in other forum I cant use the alias names in WHERE, or not this way...

[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]John_Betong_002[/eluser]
Try a variation of this which I have just ran on my localhost:

Code:
SELECT
  id, title, xrl, SUBSTRING( date, 1, 4 ) AS year
FROM
  jokes
WHERE
  SUBSTRING( date, 1, 4 ) = '2011'
ORDER BY
  date
LIMIT
  0 , 10
 
Your main problem appears to be enclosing the date in quotes.
 

[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]predat0r[/eluser]
Thank you John,

yes, i had the quotes problem first -> mysql doc example has quotes.... not always trust the docs Smile

second, i can not use alias in where, because where clause doesnt know about alias, so as you write has to repeat the part as in select statement

huh, done.

[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]Seb[/eluser]
Sorry, I overlooked the alias references in the query.

You can make references if you use "having":

Code:
SELECT
  id, title, xrl, SUBSTRING( date, 1, 4 ) AS year
FROM
  jokes
HAVING
  year = '2011'
ORDER BY
  date
LIMIT
  0 , 10


[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]predat0r[/eluser]
Anyway.. i'm not enough clever to figure out things.. Tongue

I can use YEAR() and MONTH() functions of mysql for do this query, I didn't do it because the example was written with DATE so XXXX-XX-XX and I thought I can use them only with DATE format.

FYI.

[SOLVED] query with variables - El Forum - 06-11-2011

[eluser]predat0r[/eluser]
[quote author="Seb" date="1307821400"]Sorry, I overlooked the alias references in the query.

You can make references if you use "having":

Code:
SELECT
  id, title, xrl, SUBSTRING( date, 1, 4 ) AS year
FROM
  jokes
HAVING
  year = '2011'
ORDER BY
  date
LIMIT
  0 , 10
[/quote]

thx Seb for your answare too