Trouble with: % marks in php actionscript

Mesjoggah · August 21, 2011, 8:31am

Situation:
On the frontend of my (Joomla 1.5) website, users can become member of the site by filling in a form.
Two fields in this form are more importent than others. The field Region and the field Capacity.
Region is just a simpel textfield, but capacity is a checkbox, where users can check multiple values, like 10-20, 20-30, 30-40 etc.

I have a MySql query that gives me name, adress, telephone etc, when i provide it with a region and capacity.
The part of the query where i provide region and capacity to the query, looks like this:

===================================
FROM jos_RSFORM_FORMS A
INNER
JOIN jos_RSFORM_SUBMISSIONS B
ON B.FormId = A.FormId
INNER
JOIN jos_RSFORM_SUBMISSION_VALUES C
ON C.SubmissionId = B.SubmissionId
AND C.FieldName = 'regio'
AND C.FieldValue = '134'
INNER
JOIN jos_RSFORM_SUBMISSION_VALUES D
ON D.SubmissionId = B.SubmissionId
AND D.FieldName = 'capaciteit'
AND D.FieldValue LIKE '%10-20%'
WHERE A.FormId = 2
===================================

I would now like to have a form in the backend of the website with two fields, Region and capacity. The corresponding values in the query must be provided by this form.
I have made a php actionscript, but that does not give me any results, so i did something, somewhere apparently wrong.
Here’s the script:

===================================
&lt;?php
$host = "localhost";
$user = "root";
$pass = "";
$dbname = "database";
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."&lt;BR&gt;");
mysql_select_db($dbname);
$sql = "SELECT A.FormId
     , B.SubmissionId
     , B.DateSubmitted
     , B.UserIp
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'Name'
            AND SubmissionId = B.SubmissionId ) as Name 
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'adres'
            AND SubmissionId = B.SubmissionId ) as Adres
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'telefoon'
            AND SubmissionId = B.SubmissionId ) as Telefoon 
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'email'
            AND SubmissionId = B.SubmissionId ) as Email 
  FROM jos_RSFORM_FORMS A
INNER
  JOIN jos_RSFORM_SUBMISSIONS B
    ON B.FormId = A.FormId
INNER
  JOIN jos_RSFORM_SUBMISSION_VALUES C
    ON C.SubmissionId = B.SubmissionId 
   AND C.FieldName = 'Regio'
   AND C.FieldValue = '".$_POST['Regio']."'
   INNER
  JOIN jos_RSFORM_SUBMISSION_VALUES D
    ON D.SubmissionId = B.SubmissionId 
   AND D.FieldName = 'capaciteit'
   AND D.FieldValue LIKE '".$_POST['capaciteit']."' 
 WHERE A.FormId = 2";
 
 $query = mysql_query($sql);
while ($row = mysql_fetch_array($query)) {
 echo "&lt;p&gt;",$row['Naam']," | ",$row['Adres']," | ",$row['Email']," | ",$row['Telefoon'];
}
?&gt;
======================================

I think it has something to do with the % marks in the mysql query ?
Can someone help me on this one ?

Thanks in advance,
Mesjoggah

DarthGuido · August 21, 2011, 9:09am

Try

AND D.FieldValue LIKE '%" . mysql_real_escape_string($_POST['capaciteit']) . "%'

By the way, you should validate the user input before using it in a query (like I did in the code I posted).

Mesjoggah · August 21, 2011, 11:29am

Hi Guido,

Thank you for helping me, trying to solve my form issue.
For some reasen the whole “Post” part does not seem to work.
I added a : echo $sql; to the script, to see its behaviour and noticed this:

When I add in the script region and capacity, I get a result:

====================================================

SCRIPT:

INNER
JOIN jos_RSFORM_SUBMISSION_VALUES C
ON C.SubmissionId = B.SubmissionId
AND C.FieldName = ‘Regio’
AND C.FieldValue = ‘5’
INNER
JOIN jos_RSFORM_SUBMISSION_VALUES D
ON D.SubmissionId = B.SubmissionId
AND D.FieldName = ‘capaciteit’
AND D.FieldValue LIKE ‘%10-20%’
WHERE A.FormId = 2";
$query = mysql_query($sql);
echo $sql;
while ($row = mysql_fetch_array($query)) {
echo “<p>”,$row[‘Naam’]," | “,$row[‘Adres’],” | “,$row[‘Email’],” | ",$row[‘Telefoon’];
}

RESULT:

INNER JOIN jos_RSFORM_SUBMISSIONS B ON B.FormId = A.FormId INNER JOIN jos_RSFORM_SUBMISSION_VALUES C ON C.SubmissionId = B.SubmissionId AND C.FieldName = ‘Regio’ AND C.FieldValue = ‘5’ INNER JOIN jos_RSFORM_SUBMISSION_VALUES D ON D.SubmissionId = B.SubmissionId AND D.FieldName = ‘capaciteit’ AND D.FieldValue LIKE ‘%10-20%’ WHERE A.FormId = 2

Mr. Testert nr1 | Straat 23 | email@email.nl | 1234567890

===============================================

When I use $_POST to fill in the values, i get an empty result:

SCRIPT:

INNER
JOIN jos_RSFORM_SUBMISSION_VALUES C
ON C.SubmissionId = B.SubmissionId
AND C.FieldName = ‘Regio’
AND C.FieldValue = ‘“.$_POST[‘Regio’].”’
INNER
JOIN jos_RSFORM_SUBMISSION_VALUES D
ON D.SubmissionId = B.SubmissionId
AND D.FieldName = ‘capaciteit’
AND D.FieldValue LIKE ‘%" . mysql_real_escape_string($_POST[‘capaciteit’]) . "%’
WHERE A.FormId = 2";
$query = mysql_query($sql);
echo $sql;
while ($row = mysql_fetch_array($query)) {
echo “<p>”,$row[‘Naam’]," | “,$row[‘Adres’],” | “,$row[‘Email’],” | ",$row[‘Telefoon’];
}
?>

RESULT:

INNER JOIN jos_RSFORM_SUBMISSIONS B ON B.FormId = A.FormId INNER JOIN jos_RSFORM_SUBMISSION_VALUES C ON C.SubmissionId = B.SubmissionId AND C.FieldName = ‘Regio’ AND C.FieldValue = ‘’ INNER JOIN jos_RSFORM_SUBMISSION_VALUES D ON D.SubmissionId = B.SubmissionId AND D.FieldName = ‘capaciteit’ AND D.FieldValue LIKE ‘%%’ WHERE A.FormId = 2

==============================================

How can that be ?
My PHP knowlegde is not that good that i can analyse the results properly.
I really hope that someone can help me on this !

Thanks in advance
Mesjoggah

DarthGuido · August 21, 2011, 12:45pm

The $_POST values should come from a form. Does that form have method=“POST” ?

Please post the form code as well.

Mesjoggah · August 21, 2011, 1:20pm

Hi Guido,

Tank you for pointing me into the right direction.
The form is a RSForm on a Joomla webiste.
Method is Post, but…RSForm is messing a bit with the “names”

So in stead of keeping the name i provided when creating the form, RS form changed name=“Regio” into name=“form[Regio]” and Capaciteit into: name=“form[Capaciteit]”

I managed to change the “Regio” part, and that is working now:
AND C.FieldValue = ‘" . $_POST[‘form’][‘Regio’] . "’
(I will secure this injection when finished…

But, the Capaciteit value has een extra added to it.
I tried some things like:

AND D.FieldValue LIKE ‘%" . mysql_real_escape_string($_POST[‘form’][‘capaciteit \[\]’]) . "%’

But that does not work…
So if you would be willing to help me on this last hurdle, i would be very grateful !

Mesjoggah

DarthGuido · August 21, 2011, 1:26pm

Do a print_r($_POST); to see what exactly is contained in the $_POST array.

Mesjoggah · August 21, 2011, 1:56pm

That gives me this:

Array ( [form] => Array ( [Regio] => 5 [Capaciteit] => Array ( [0] => 10-20 ) [Verstuur] => Verstuur [formId] => 7 ) )

DarthGuido · August 21, 2011, 2:02pm

Try:
$_POST[‘form’]['capaciteit][0]

I wonder why Joomla adds that third index though. Is it possible to check more than 1 value for capaciteit? And if so, what do you want to do if the user does check more than 1 value?

Mesjoggah · August 21, 2011, 2:12pm

Hi Guido,

Yes, but capacity is a checkbox, where users can check multiple values, like 10-20, 20-30, 30-40 etc.

So, when lots of users have filled in their region and (multiple) capacities, i want to be able, by entering a certain region, and 1 capacity value to get the names etc. from those who have filled in, besides other values, this 1 particular value.

Greets,
Mesjoggah

DarthGuido · August 21, 2011, 2:16pm

In the case of this form, what do you want the code to do when multiple capacity values have been checked in the form?
Try checking multiple values, and see what happens to the content of $_POST (do the print_r again).

Mesjoggah · August 21, 2011, 2:30pm

Hi Guido,

You must see the website as intermediate between customers and companies.
The customer tells me he needs a hall for 233 people.

In the MySQL database are companies stored that have entered the capacities of their conferencerooms, weddinghalls etc.

So in the backend of the site, i fill in the region of the applicant and, because he needs room for 233 people, i’ll check capacity 200-250.

When i hit the submit button, i would like all the companies in the region, that meet the requirements, to show up on my screen.

So, the companies will be able to check multiple capacities,
I will only fill in 1 capacity. So the form that i would like to see in action, has multiple choices, but their will never be checked more that 1 option.

Mesjoggah · August 21, 2011, 2:40pm

Ah ! That makes defintely sense !!!
I’ll try it within the next hour !

Thank you very, very much !

Mesjoggah

DarthGuido · August 21, 2011, 2:36pm

So the form the companies will use to add their data to the database will have multiple checkboxes for capacity, and the code that receives the data from that form will handle the multiple values.

The form that you use to get all companies that meet the requirements, capacity shouldn’t have multiple checkboxes, but radio buttons, or a drop down menu, which means you’ll be able to select only 1 value.
Of course, you can leave the checkboxes, and use the solution a gave before:
$_POST[‘form’]['capaciteit][0]
This way you’ll always use the first checkbox checked.

Mesjoggah · August 21, 2011, 3:58pm

Hi, Guido

I tried it, and it works.

Thanks a lot !!

Mesjoggah

Topic		Replies	Views
Php code mysql PHP	5	595	October 8, 2014
Actionscript, php, mySQL dilemma. Please help! Content graphics	4	631	June 28, 2010
Establishing a connection to my Database in mysql PHP	3	823	October 8, 2014
Include form result in SELECT statement PHP	1	411	December 12, 2011
How would this be done? PHP	3	807	September 6, 2010

Trouble with: % marks in php actionscript

Related topics