custom php function in mysql query

Question

my query below

working query

$sorgum = "SELECT kolon_etiketler, kolon_yazar, kolon_baslik, kolon_resim_baslik, kolon_resim_url, kolon_yazi, kolon_ytarihi FROM tb_yazilar WHERE kolon_statu = 'onay' AND kolon_yazar ='".tire_bosluk_olsun($_GET["rd_ozne"])."' AND kolon_baslik ='".tire_bosluk_olsun($_GET["rd_nesne"])."'  ORDER by kolon_onaytarihi DESC";

NON-working query

$sorgum = "SELECT kolon_etiketler, kolon_yazar, kolon_baslik, kolon_resim_baslik, kolon_resim_url, kolon_yazi, kolon_ytarihi FROM tb_yazilar WHERE kolon_statu = 'onay' AND kolon_yazar ='".tire_bosluk_olsun($_GET["rd_ozne"])."' AND noktalamasiz(kolon_baslik) ='".tire_bosluk_olsun($_GET["rd_nesne"])."'  ORDER by kolon_onaytarihi DESC";

difference btw working and non-working one

working: AND kolon_baslik = ...
non-working: AND noktalamasiz(kolon_baslik) = ...

my linking process

user enters article title into db in normal written form
when page links the article, uses author-title info of the article. Normal written form is overwritten with custom function to remove punctuation & replace space with dash

my aim

from address bar, $_GET['nesne'] is coming. This is the without-any-punctuation syntax of article's title. Also space character is replaced with dash character.
In my MySQL table, article title is in normal form with punctuation and with spaces btw words.
example:
in my MySql table: "Is John's Clock Working?", it becomes in url address "Is-Johns-Clock-Working"

my question

Is there any thing I can do with non-working query? I can't give to its removed punctuation to $_GET['nesne'] so I need to compare the values of without-punctuation-state of the title row in mysql table and $_GET['nesne']. Maybe I am on very wrong way so please lead me the correct way to handle automatic way of linking with allowing user to enter punctuated titles and only 1 title column in mysql table.

edit

noktalamasiz = a custom-php function that removes all punctuation.

tire-bosluk-olsun = replace the dash with space. So if my very first title doesn't include any punctuation but only space btw words, then I would have no difficulty and only use working sql.

function tire_bosluk_olsun ($tireli)
{
$tireli = trim($tireli);
$tireli = str_replace('-',' ',$tireli);
return $tireli;
}

function noktalamasiz($noktalamali) {
$noktalamali = trim($noktalamali);
$ara = array('.',',',';',':','...','?','!','-','—','/','\\','“','”','‘','’','"','\'','(',')','[',']','’','{','}','*','&','#','^','<','>','|');
$degistir = array('','','','','','','',' ','','','','','','','','','','','','','','','','','','','','','','',);
$noktalamali = str_replace($ara,$degistir,$noktalamali);
return $noktalamali;
}

what is non-working

if my query involves noktalamasiz custom function as this : noktalamasiz(kolon_baslik); then I got empty screen without any warning- notice or error. I am working with -1 error level.

whole related php code

$sorgum = "SELECT kolon_etiketler, kolon_yazar, kolon_baslik, kolon_resim_baslik, kolon_resim_url, kolon_yazi, kolon_ytarihi FROM tb_yazilar WHERE kolon_statu = 'onay' AND kolon_yazar ='".tire_bosluk_olsun($_GET["rd_ozne"])."' AND noktalamasiz(kolon_baslik) ='".tire_bosluk_olsun($_GET["rd_nesne"])."'  ORDER by kolon_onaytarihi DESC";

if ($beyan = $db_baglanti->prepare($sorgum)) 
{

    /* execute statement */
    $beyan->execute();

    /* bind result variables */
    $beyan->bind_result($etiketler, $yazar, $baslik, $resim_baslik, $resim_url, $yazi, $ytarihi);

    /* fetch values */
    while ($beyan->fetch()) 
    {
        echo '<div class="sol-icerik-kapsar">'."\r\n";
        echo "\t".'<h1>'.$baslik.'</h1>'."\r\n";
        echo "\t".'<img class="mansetresim" width="120" height="160" src="'.sitenin_koku.'img/manset/'.$resim_url.'" alt="'.$resim_baslik.'" title="'.$resim_baslik.'" />'."\r\n";
        echo "\t".'<p><a href="'.sitenin_koku.'yazılar/'.bosluklar_tire_olsun($yazar).'">'.$yazar.'</a>'.' - '.turkcetarih('j F Y',$ytarihi).'</p>'."\r\n";
        echo "\t".'<p>'.$yazi.'</p>'."\r\n";
        echo "\t".'<p>'.$etiketler.'</p>'."\r\n";
        echo '</div>'."\r\n";
    }
    /* close statement */
    $beyan->close();
}

Also could you post the mysql error of the non-working query? — Nelson, Mar 24 '13 at 13:47
And what does `tire_bosluk_olsun` do? Does it call `mysql_real_escape_string`? — Marcel Korpel, Mar 24 '13 at 13:48
Can you specify what "non working" is? Does it return an error, does it return the wrong results, does it return only a subset?. Further more, this is the mother of all injection issues (unless that 'tire_bosluk' thing is some sort of escape): never just put something from $_GET into your query. It's bad for, even if it's just for starting. You shouldn't "add security later". Read up on parameterized queries! — Nanne, Mar 24 '13 at 13:49

score 2 · Accepted Answer · answered Mar 24 '13 at 14:01

2

You can't use php functions in mysql query. That's not how it works. For your purpose I'd create a new row which is filled with output of your php function noktalamasiz().

answered Mar 24 '13 at 14:01

Martin.

10,494
3
42
68

score 1 · Answer 2 · answered Mar 24 '13 at 14:00

1

There is no way you can run a "custom php function" in your MySQL query. You should run the function outside the query, get a return value from it, and then use that value in your query. So your query should look like this:

$kolon_baslik = //get your field value first;

$sorgum = "SELECT kolon_etiketler, kolon_yazar, kolon_baslik, kolon_resim_baslik, kolon_resim_url, kolon_yazi, kolon_ytarihi FROM tb_yazilar WHERE kolon_statu = 'onay' AND kolon_yazar ='".tire_bosluk_olsun($_GET["rd_ozne"])."' AND " . noktalamasiz($kolon_baslik) . " ='".tire_bosluk_olsun($_GET["rd_nesne"])."'  ORDER by kolon_onaytarihi DESC"

answered Mar 24 '13 at 14:00

mavili

3,385
4
30
46

please also lead me the correct way to handle automatic way of linking with allowing user to enter punctuated titles and only 1 title column in mysql table. – Andre Chenier Mar 24 '13 at 14:02
I have included the correct way of calling your php function for the query in my answer now. – mavili Mar 24 '13 at 14:03
I have now edited the code. check if that will do what you're trying to do. – mavili Mar 24 '13 at 14:06
Parse error: syntax error, unexpected 'if' (T_IF) in ... on line 5. line 5 is: if ($beyan = $db_baglanti->prepare($sorgum)) – Andre Chenier Mar 24 '13 at 14:08
you have some php syntax error. that tells you, you're using an if statement somewhere that you shouldn't be using. can you put your code around that error. the error tells you what file it is, and what line the error is in. – mavili Mar 24 '13 at 14:44

score 1 · Answer 3 · answered Mar 24 '13 at 14:08

1

You need to run the function before the query as what you're doing is not allowed:

First, run a select statement to get the field kolon_baslik. Next, use your function to clean the input:

$cleanInput = noktalamasiz($kolon_baslik); /* Assumed you've already gotten the value */

Finally, run your query with the cleaned input:

$sorgum = "SELECT kolon_etiketler, kolon_yazar, kolon_baslik, kolon_resim_baslik, kolon_resim_url, kolon_yazi, kolon_ytarihi FROM tb_yazilar WHERE kolon_statu = 'onay' AND kolon_yazar ='".tire_bosluk_olsun($_GET["rd_ozne"])."' AND '" . $cleanInput . "' ='".tire_bosluk_olsun($_GET["rd_nesne"])."'  ORDER by kolon_onaytarihi DESC";

By the way, you can clean up your function:

function noktalamasiz($noktalamali) {
$noktalamali = trim($noktalamali);
$ara = array('.',',',';',':','...','?','!','-','—','/','\\','“','”','‘','’','"','\'','(',')','[',']','’','{','}','*','&','#','^','<','>','|');
$noktalamali = str_replace($ara,'',$noktalamali);
return $noktalamali;
}

answered Mar 24 '13 at 14:08

What have you tried

11,018
4
31
45

I don't have $kolon_baslik so I only have $_GET from url as an input to compare with my MySql table row value. – Andre Chenier Mar 24 '13 at 14:15
You need to run another select query to retrieve the value of `kolon_baslik` - then use that value as shown above. – What have you tried Mar 24 '13 at 14:18
ok Evan, I will try. Thank you for the idea. No select query can retrieve the value of kolon_baslik because I can not give proper WHERE command. So maybe I utilize from SESSION variable to move kolon_baslik from one page to others where I need. – Andre Chenier Mar 24 '13 at 14:22
Sure, it's a really simple concept, you just want to run the function on the data *prior* to making the second (main) query. You're doing everything correctly, just out of order. – What have you tried Mar 24 '13 at 14:23

score 1 · Answer 4 · answered Mar 24 '13 at 14:10

Firstly, noktalamasiz is not a mysql function and as you told it's a php function so it can't be called as a string literal and not just in mysql, it is applied for all. Secondly, you cannot use any aggregate(or inbuilt) mysql function with 'WHERE' clause.