inserting data into a sting

[Destramic]

hey guys...basically i have a query which looks like:

 

SELECT name, dob
FROM users
ORDER BY dob DESC 

 

but what i want to do is to get this string and place

 

WHERE name = 'destramic'

 

inbetween FROM users  and ORDER BY dob DESC using regex so that it looks like

 

SELECT name, dob
FROM users
WHERE name = 'destramic'
ORDER BY dob DESC 

 

can someone please help me....thanks you

 

 

 

[Pikachu2000]

So what's the problem, then?

[Destramic]

well i want to be able to insert

 

WHERE name = 'destramic'

 

inbetween

 

SELECT name, dob
FROM users

and

ORDER BY dob DESC 

 

but i want to do it using regex but i dont know how to do it...so im after the help please

[cags]

Is there something more dramatic that you just haven't mentioned, because I really don't see why regex would be used for this. You could achieve what your after simply by str_replace'ing FROM users with FROM users WHERE ...

[Destramic]

but the thing is the name of the table could be anything.

 

so...

 

SELECT name, dob
FROM users
ORDER BY dob DESC 

 

needs to have

WHERE name = 'destramic'

added into the string to complete the sql query

 

SELECT name, dob
FROM users
WHERE name = 'destramic'
ORDER BY dob DESC 

 

i hope you understand

[Destramic]

in fact ive been having a little reading about it and im no expert yet but something like this i need if you can help....bare in mind it obviously dont work

 

$query = 'SELECT name, dob
FROM users
ORDER BY dob DESC 
';

$replace = "WHERE name = 'destramic'";

$test = preg_replace('(FROM)\s[a-zA-Z0-9](.*)(ORDER\S+BY)', $replace, $query);

 

generate this:

 

SELECT name, dob
FROM users
WHERE name = 'destramic'
ORDER BY dob DESC 

[xyph]

A MySQL SELECT query can be so complex and in so many different variations. You're doing it wrong man Where is this initial query being generated? Why not just hold the variables being used to generate it, and rather than trying to inject the WHERE clause into it, generate the whole query WITH the where clause.

 

Here's what a proper RegEx would have to deal with if you wanted to put the WHERE clause in the right spot every time

 

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [sTRAIGHT_JOIN]
      [sql_SMALL_RESULT] [sql_BIG_RESULT] [sql_BUFFER_RESULT]
      [sql_CACHE | SQL_NO_CACHE] [sql_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [iNTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

 

This is assuming there's no UNION either.

[Destramic]

is see...its just my query is too complex to break down for my mysql db class.

 

	public function fetch_league($game_name, $league_name)
{		
	$variables = array('rank'    => '0',
	                   'points'  => '0',
	                   'drawing' => '0');

	$query = "SELECT *,
                     @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @drawing := IF(@points = points, IF(@points = '0', @drawing = '0', @drawing = '1'), @drawing = '0'),
                         IF(@drawing = '1', @rank + 1, @rank) as rank,
                         @drawing := '0'
                  FROM(
                  SELECT t.team_id,
                         t.team_name,
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                  FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  WHERE tl.league_id = :1
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x";

	$league = $db->set($variables)->fetch_all($query, '1');
	$rows   = $league->get_rows();

	return $rows;
}

 

thats were i was thinking of having a where() function that could inject into the query