PHP regex question

[black-ops]

Ok, so I currently have a system in place where in the database you have an item. And a reference name in the database to another field.

Like so:
Item Name, Cal by
Shoes VAT

So my php will run through check the Cal by is VAT then go to a single database which has the VAT % and use that value in a calculation.

However I also wish to use things such as
VAT * X / Y
within my database so the return of the query would be the above. Then use a regular expression to split up the three components such as:

VAT,
X,
Y

And using the delimiters from the database carry out their associated mathematical function.

VAT * X / Y

So far I know this works

<?php

$getCalBy = mysql_query("SELECT * FROM myTable WHERE ...");
$result = mysql_result($getCalBy, 0, 'Cal By');

?>

So far this is fine for any single entry however I need to identify the individual values, any helping hands out there?

 

[Tetsujin]

Ok. This sounds like a bad solution to the problem, but I'm not 100% sure what the problem actually is so I can't advise a better one. As such...

First I'd say that in your "formula" column you might want to make it clear which 'words' should relate to other columns and which are string literals, so I'd recommend adding a prefix. So instead of storing "VAT * 100 / 50" you'd use "%VAT * 100 / 50" or whatever. So all your "this means a column name" words get prefixed with something; % is a common one.

Incidentally, don't really know what "Cal by" means, or why you're using spaces in column names, or why you're mixing case in column names, or then why "Name" starts uppercase but "by" doesn't. No spaces (use underscores if needed) and all lowercase makes things much tidier, in my view

Next, using mysql_result() is slow and a pita; better to use mysql_fetch_assoc(). So my loop would look something like this:

//first pull out all the "VAT" and so on, actual values from whatever your reference table's called
$variables = array();
$r_variables = mysql_query("SELECT name,value FROM yourReferenceTable");
while($r_variables && $variable = mysql_fetch_assoc($r_variables))
    $variables[$variable['name']] = $variable['value'];

//next we can do the replacements for each product in turn
$r_products = mysql_query("SELECT id,`Item Name`,`Cal by` FROM myTable WHERE...");
while($r_products && $product = mysql_fetch_assoc($r_products)) {
    print 'The product name is <b>'.$product['Item Name'].'</b><br>';
    $price = replaceVariables($product['Cal by'], $variables);
    print 'The price is <b>&pound;'.number_format($price, 2).'</b>';
}

function replaceVariables($formula, $vars) {
    while(eregi("%[a-z0-9_]+", $formula, $match)) //find a var name; might need to put a backslash before the %, can't remember
        $formula = str_replace($match[0], $vars[substr($match[0], 1)], $formula);
    return eval('return '.$formula.';');
}

This would work, but it's a bit messy.

 

[black-ops]

wasn't eregi deprecated though? Although this works fine, Thanks! I think I will have to use preg instead as the deprecation is of 5.3.0?

Oh also I managed to simplify it down:

            // We check for % as this will tell us what the reference name is...
            eregi("%[a-z0-9_]+", $test_string, $out);
            // We want to now remove the % from the string.
            $reference = str_replace('%','',$out);
            // Cycle through the arrays finding the correct associated values.
            $replacement_string = mysql_result($iRefSQL,0,$reference[0]);
            // Now we want to replace the modified values back into the original string...
            // This will give us the formula we need!
            $final_result = eregi_replace("%[a-z0-9_]+",$replacement_string,$test_string);

However it will only take things from a string such as VAT + 5...
Nothing like (VAT / SOMETHING)/(10+2)

 

[Tetsujin]

It will if you define SOMETHING in your reference table

 

[black-ops]

It will if you define SOMETHING in your reference table

let say VAT is 5000 it will for some reason assign SOMETHING as being 5000 too :/

 

[Tetsujin]

Well yes, I realise now, why: you broke it by "simplifying". The eregi() needs to be inside the if().

And yeah eregi should probably be replaced with preg_match("/blah/i", $blah, $match); but I'm not 100% on the syntax off the top of me head

and why are you back to using mysql_result?

 

[black-ops]

Well yes, I realise now, why: you broke it by "simplifying". The eregi() needs to be inside the if().

And yeah eregi should probably be replaced with preg_match("/blah/i", $blah, $match); but I'm not 100% on the syntax off the top of me head

and why are you back to using mysql_result?

Ok well basically the system I have made is quite a large one. What is basically happening is the page before someone selects a "category" from a certain relational tree on this page a query is made to find out the original parent of all the selections made. (over 5000 entries in the db of the relationships).

The second highest tree has a value that is calculated from a user input screen. This value contains a calculation string in the db. What I need to do is to be able to retrieve that calculation string and fill it with the values entered on the previous page. This will then give me a total value.

It's an odd concept I know but it's what the application requires. I would do it another way but my boss is insisting on this method of data flow.

Seeing as I have never really used this method before I have hit a brick wall.
The longest calculation string so far is:

(%A x %B)/(%C * %D)

Now my script so far does %A but it then fills B,C,D with the value of %A which is what i'm trying to fix at the moment.

 

[Tetsujin]

Now my script so far does %A but it then fills B,C,D with the value of %A which is what i'm trying to fix at the moment.

Because you removed the "if" block from around my "eregi". If you put it back, it'll work, or at least, break in a different way (probably only replacing the first entry again) but this way is the way it needs to break to be able to debug it.

I'd also need to see more lines than the 4 you posted above.

 

[black-ops]

Because you removed the "if" block from around my "eregi". If you put it back, it'll work, or at least, break in a different way (probably only replacing the first entry again) but this way is the way it needs to break to be able to debug it.

I'd also need to see more lines than the 4 you posted above.

Yeh I removed the if block for some reason think it bugged out. Will put it back in see what's going on. I will probably call a break on this in a bit, been working all day and my head is full of confusion hah... will let you know if it works!

 

[sparkey196]

I have a strong suspicion your database structure is all wrong, and even if you get this working it will be a hack job.

I would strongly advise making a full ERD with mysql workbench and posting it here for review before doing any more code. Failure to prepare is to preparation to fail!

A good way to learn the fundamentals is by example. Download the Northwinds Database and study the relationships and even the basic naming conventions.

Once it call clicks there will be no more trial and error coding and no more head banging against the wall.

 

[black-ops]

I have a strong suspicion your database structure is all wrong, and even if you get this working it will be a hack job.

I would strongly advise making a full ERD with mysql workbench and posting it here for review before doing any more code. Failure to prepare is to preparation to fail!

A good way to learn the fundamentals is by example. Download the Northwinds Database and study the relationships and even the basic naming conventions.

Once it call clicks there will be no more trial and error coding and no more head banging against the wall.

it's not the structure of the database that is wrong, the system flow is exactly that way. let's not forget there's no real true relationships within the phpmyAdmin structure... if anything it's all psuedo.

The system functionality works perfectly it's this segment of the system was not thought out by our project leader... I sorted it another way in the end

By taking the title of a column and comparing in the way of the formula

for example, we have 5 columns called A, B, C, D, E

and in the formula we have

(A+B)/C+D+E

I would then check the strpos of each matching pair and replace the the match with a result of the column -> row which matches the users credentials.

This would then result in

(1 + 5)/19+5+3

I could then use EVAL on this to make that string into a calculation. The great thing about this although EVAL can be exploited this won't get exploited because there is NO user input anymore what so ever.

Regards,