We have a database with millions of products and prices. We're trying to add a filter onto our sql statement so that we can narrow down what we're looking for and then display it on our table. Some of the things we wanted to filter are related to pricing which is giving us a problem.
Since our prices are fairly dynamic, we currently use a php function to calculate our fees in real time rather than store them inside the database. We pass the current price through the function and then display it in the table:
echo profit('99.99'); // output: 12.19
The problem with this is we can't run these calculations inside the query using our function to find results:
$query = "SELECT * FROM `products` WHERE ".profit(."`price` + `shipping` + `acquisition` + `fees`".)." > 10 ";
Is there anyway to get around this? Do we need to iterate through all the table values? We're trying to avoid this since the database is so large. Thanks for any help!
You need to fetch the values and process them in your application. You cannot pass a PHP function to MySQL.
If possible, use whatever SQL commands can perform an equivalent operation. MySQL supports a number of functions that could probably do what you want.
Generally what this means is you need to create a profit column, populate it, index it, then use it in your query:
SELECT * FROM products WHERE profit > 10
As to how you go about populating this, it could be as easy as:
UPDATE products SET profit=(price - shipping - acquisition - fees)
It's not clear why you are adding these numbers up in the first place.
You can create a function in MySQL and make this function estimate the profit for you.
I don't know how complex your profit function is since you don't show the code. If your "profit" function is changing constantly: you're doing it wrong. Code shouldn't change all the time and it is likely you should change its design.
In any case, here's how to create functions in MySQL http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
I would go with above solution. You could also go ahead and at a column that indicates the profit. Update said column whenever the profit might change. This is very bad if products table is somewhat big (note: by somewhat big I mean over 100 thousand rows). You could just update them on a batch process and it would just take about a couple of minutes if the column isn't indexed, and not much if it is. If you have over 100 thousand rows of products, that means your store is HUGE and there is no excuse for not using above solution for the simplicity of adding a column.
Worst case escenario, if your functions are very complex and you need something more potent than what MySQL offers (this is very highly unlikely), you can make a function in C and attach it to MySQL. Here is a tutorial I just found. I haven't validated it, but it seems clear and of quality: http://blog.loftdigital.com/blog/how-to-write-mysql-functions-in-c
Lets assume you have write your sql like this :
$query = "SELECT *
FROM `products` ;
And that returns you a 2D array named $resultwhich is kind of like this :
array (
"0" => array (
'price'=>100,
'shipping'=>10,
'acquisition'=>10,
'fees'=>'10'
),
"1" => array (
'price'=>200,
'shipping'=>20,
'acquisition'=>20,
'fees'=>'20'
)
)
Now as you have one existing php function to calculate profit you can do this by looping over the array:
$expectedResult = array();
foreach ($result as $row) {
if (profit ($row ['price'] + $row ['shipping'] + $row ['acquisition'] +$row ['fees' ) > 10 )
$expectedResult [] = $row ;
}
You will get only your desired rows in the $expectedResult array here.
Note: Though it is a solution it is a bad one. Good solution can be like these :
That will do your trick here without changing much :) Happy coding :)
I have created a simple php function to use MySQL queries.
Any query can be executed in 1 simple function.
In case of select query, We can get the selected arguments as variable name contains the selected argument value.
For ex :
<?php
q("select user_name,email_id from users where user_id=48");
echo $user_name; echo "<br>";
echo $email_id;
?>
or you can set your own variable name by putting " as "
<?php
q("select user_name as uname, email_id as email from users where user_id=48");
echo $uname; echo "<br>";
echo $email;
?>
result output will be :
someuser
someemail
If more number of rows has been selected, the variable name will be created as an array for ex :
<?php
q("select user_name,user_id from users");
for($n=0;$n<count($user_name);$n++)
{
if(count($user_name)==1) // if single row is selected
{
$username_val=$user_name;
$user_ids=$user_id;
}else{
$username_val=$user_name[$n]; // for multiple rows selected
$user_ids=$user_id[$n];
}
echo $username;
}
?>
or you can set your own variable name by putting " as "
<?php
q("select user_name as un,user_id as uid from users");
for($n=0;$n<count($user_name);$n++)
{
if(count($user_name)==1) // if single row is selected
{
$username_val=$un;
$user_ids=$uid;
}else{
$username_val=$un[$n]; // for multiple rows selected
$user_ids=$uid[$n];
}
echo $username_val; echo " ";
echo $user_ids; echo "<br>";
}
?>
Result output will be : (If the user table has three rows )
User1 4043
User2 4048
User3 4056
Create mysql Connection file ex : mysql_connect_file.php
<?php
$dbc=new mysqli('localhost', 'my_user', 'my_password', 'my_db');
?>
The php function is below
<?php
require_once './mysql_connect_file.php';
function q($q)
{
$main_q=$q;
$q= strtolower($q);
global $dbc;
$temp=$q;
$temp=str_replace(" ", "", $temp);
$temp= strtolower($temp);
$temp=".$temp";
if(strpos($temp, "update")==1 || strpos($temp, "insert")==1 || strpos($temp, "delete")==1 || strpos($temp, "alter")==1 || strpos($temp, "create")==1)
{
$rd2= mysqli_query($dbc,$main_q);
if($rd2)
{
return TRUE;
}
else{
$mysql_err= mysqli_error($dbc);
$err= debug_backtrace();
$err_line=$err[0]['line'];
$err_file=$err[0]['file'];
echo "<font color='black'>Error at <b>$err_file on line $err_line </b>query --></font><font color='maroon'>$main_q</font> (<font color='red'> $mysql_err </font> )";
return FALSE;
}
}elseif(strpos($temp, "select")==1){
$qn= str_replace("select ", "", $q);
$qn=substr($qn,0, strpos($qn, " from"));
$qn="$qn,";
$selc= str_replace("`","", $qn);
$qn= str_replace("`","", $qn);
$my_var=array();
$my_nm=array();
for($m=1;$m<=substr_count($selc, ',');$m++)
{
$my_nm[$m]=substr($qn,0, strpos($qn, ","));
$qn=substr($qn,strpos($qn, ",")+1, strlen($qn));
if(strpos($my_nm[$m]," as ")>0)
{
$my_var[$m]= str_replace(" as ", "~", $my_nm[$m]);
$my_var[$m]= str_replace(" ", "", $my_var[$m]);
$my_var[$m]=substr($my_var[$m],strpos($my_var[$m],"~")+1,strlen($my_var[$m]));
}else
{
$my_var[$m]=substr($my_nm[$m],0, strlen($my_nm[$m]));
$my_var[$m]= str_replace(" ","", $my_var[$m]);
}
}
$rn=mysqli_query($dbc, $main_q);
if($rn)
{
if(mysqli_num_rows($rn)>0)
{
for($t=1;$t<=count($my_var);$t++)
{
$$my_var[$t]=array();
}
while($row=mysqli_fetch_array($rn,MYSQLI_ASSOC))
{
if(mysqli_num_rows($rn)>1)
{
for($t=1;$t<=count($my_var);$t++)
{
${$my_var[$t]}[]=$row[$my_var[$t]];
}
}else{
for($t=1;$t<=count($my_var);$t++)
{
$$my_var[$t]=$row[$my_var[$t]];
}
}
}
if(mysqli_num_rows($rn)>1)
{
for($t=1;$t<=count($my_var);$t++)
{
$GLOBALS[$my_var[$t]]= sel_mr($my_var,$$my_var[$t]);
}
for($t=1;$t<=count($my_var);$t++)
{
return $$my_var[$t];
}
}
if(mysqli_num_rows($rn)==1)
{
for($t=1;$t<=count($my_var);$t++)
{
$GLOBALS[$my_var[$t]]=$$my_var[$t];
}
for($t=1;$t<=count($my_var);$t++)
{
return $$my_var[$t];
}
}
}else
{
for($t=1;$t<=count($my_var);$t++)
{
$GLOBALS[$my_var[$t]]=NULL;
}
for($t=1;$t<=count($my_var);$t++)
{
return $my_var[$t];
}
}
}else
{
for($t=1;$t<=count($my_var);$t++)
{
$my= mysqli_error($dbc);
if($t==1)
{
$err= debug_backtrace();
$err_line=$err[0]['line'];
$err_file=$err[0]['file'];
echo "<font color='#ef0000'>Error at <b>$err_file on line $err_line </b>query --></font><font color='maroon'>$q</font> (<font color='red'> $my </font> )";
}
}
for($t=1;$t<=count($my_var);$t++)
{
for($p=0;$p<count($$my_var[$t]);$p++)
{
$a=$$my_var[$t];
return $a;
}
}
}
}
}
function sel_mr($a,$ab)
{
for($t=1;$t<=count($a);$t++)
{
foreach ($ab as $my)
{
${$a[$t]}[]=$my;
}
}
for($t=1;$t<=count($a);$t++)
{
return $$a[$t];
}
}
?>
Notes :
You can save this code into a file then you can call this function by including that file name
for ex : if your file name is q.php ( --> contains q function ) then you can use the code for another files by including
<?php
include 'q.php';
q("select user_name from users where user_id=4048");
echo $user_name
?>