Database queries

Report · Apr 04, 2011

Hi I'm new to Dreamweaver and building websites. I am building a website where in a form you enter a date and select a name from an select list then press the submit button. What I want is to send a query to my database and check to see if the name you selected is available on that date, and if so post on the same form that the person is either available on that date or not.

I have created my database and I am connected to it. I have created a recordset, with a preset name and date, and when I test my sql it shows one record from my database.

I hope I have enough info here and I am not to confusing.

Thanks in advance for any help.

Here is a copy of the function that was created when I created my recordset

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

mysql_select_db($database_connEMJEvents, $connEMJEvents);

$query_rsAvailability = "SELECT * FROM `Bookings` WHERE `speaker` = 'John Doe' AND `date` = '2011-04-09'";

$rsAvailability = mysql_query($query_rsAvailability, $connEMJEvents) or die(mysql_error());

$row_rsAvailability = mysql_fetch_assoc($rsAvailability);

$totalRows_rsAvailability = mysql_num_rows($rsAvailability);

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

?>

So on my form I have

textbox --- where you enter date

option list -- where you select a name

submit button

some text where i want to change to available or not abavailable

What I don't know is what to set the action of my form to and how to have my form select the date and name and query the database with that information then change the label to show available or unavailable as a result of the query.

Thanks

Winrol

Report · Apr 04, 2011

winrol wrote:
What I don't know is what to set the action of my form to

Select the PHP document which executes the query.

So on my form I have
textbox --- where you enter date
option list -- where you select a name
... and how to have my form select the date and name and query the database with that information

Let´s assume that the *name* of your....

a) input field is "date"

b) list is "speaker"

...and that you´re passing the data from your form using the (default) POST method. In this case you´ll need to edit the Recordset Filter Criteria and have both the "speaker" and the "date" colums retrieve the values from the corresponding Form Variables "speaker" and "date".

Report · Apr 04, 2011

I am using index.dwt.php that has the from and I want to execute it from here as I need to make other php pages for my web site as well.

Sorry, yes you are correct the inputfield is "Date" and the list is "Speaker". My recordset is call rsAvailability. Here is the code that was created when I made the recordset.

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

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

mysql_select_db($database_connEMJEvents, $connEMJEvents);

$query_rsAvailability = "SELECT * FROM `Bookings` WHERE `speaker` = 'Don Narcisse' AND `date` = '2011-04-09'";

$rsAvailability = mysql_query($query_rsAvailability, $connEMJEvents) or die(mysql_error());

$row_rsAvailability = mysql_fetch_assoc($rsAvailability);

$totalRows_rsAvailability = mysql_num_rows($rsAvailability);

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

?>

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

Now I am assuming that the top part of this function is just seeing if the "GetSQLValueString" exists. I am not sure of what value the $theValue is variable is getting?

<?php

if (!function_exists("<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

This line selects the database with the connection

mysql_select_db($database_connEMJEvents, $connEMJEvents);

This line is the actual query. Would I create variables ie:$Speaker and $Dates and put them in the place of "John Doe" and "2011-04-09"

$query_rsAvailability = "SELECT * FROM `Bookings` WHERE `speaker` = 'John Doe' AND `date` = '2011-04-09'";

So it would be --- $query_rdAvailability= "SELECT * FROM Bookings WHERE speaker=" $Speaker" +"AND date = "$Dates";

The next 3 lines I am not sure what they are doing

$rsAvailability = mysql_query($query_rsAvailability, $connEMJEvents) or die(mysql_error());

$row_rsAvailability = mysql_fetch_assoc($rsAvailability);

$totalRows_rsAvailability = mysql_num_rows($rsAvailability);

Now I don't know why this is repeating :

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

?>

So once my query has ran and has come back with an answer either John Doe is booked on that day or he is not. How would I check this result and then decide with what answer to replace the "Label text" with".

My form is called frmAvialability and my label is called Availability. I would have to write an if/else statement:

if(database result variable =="") {

frmAvailability.Availability="True";

}

else {

frmAvailability.Availability="False";

}

Could I put this in a php statement just under the above function?

Thanks

Winrol

Report · Apr 04, 2011

winrol wrote:
This line is the actual query. Would I create variables ie:$Speaker and $Dates and put them in the place of "John Doe" and "2011-04-09"
$query_rsAvailability = "SELECT * FROM `Bookings` WHERE `speaker` = 'John Doe' AND `date` = '2011-04-09'";

Yes, those static "test" values need to be replaced, but you should rather do the replacement by double-clicking the "Availability" in the Server Behaviors panel and choose a columname = Form Variable [form_element_name] Filter Condition for both columns.

Now I am assuming that the top part of this function is just seeing if the "GetSQLValueString" exists. I am not sure of what value the $theValue is variable is getting?

At this early stage you don´t have to care about this -- what´s more important to know is, how the query code looks like after you made the changes.

So once my query has ran and has come back with an answer either John Doe is booked on that day or he is not. How would I check this result and then decide with what answer to replace the "Label text" with".

Let´s better discuss this after the modified Recordset works as expected 😉

Report · Apr 04, 2011

Ok, I changed my record set this is what I entered into the sql box:

SELECT speaker, `date`

FROM Bookings

WHERE speaker = colname AND Bookings.date = colname2

When I was editing it in the record set dialog box when I had the first filter in, speaker, then tested it and had to give a name then I got all the records with the speaker name. I had to go to the advanced tab and added the second filter when I tested it nothing came up to ask for a name and date and I got nothing and there should be 1 record there .

This is the output:

$colname_rsEMJEvents = "-1";

if (isset($_POST['speaker'])) {

$colname_rsEMJEvents = $_POST['speaker'];

}

$colname2_rsEMJEvents = "-1";

if (isset($_POST['date'])) {

$colname2_rsEMJEvents = $_POST['date'];

}

mysql_select_db($database_connEmjEvents, $connEmjEvents);

$query_rsEMJEvents = sprintf("SELECT speaker, `date` FROM Bookings WHERE speaker = %s AND Bookings.date = %s", GetSQLValueString($colname_rsEMJEvents, "text"),GetSQLValueString($colname2_rsEMJEvents, "date"));

$rsEMJEvents = mysql_query($query_rsEMJEvents, $connEmjEvents) or die(mysql_error());

$row_rsEMJEvents = mysql_fetch_assoc($rsEMJEvents);

$totalRows_rsEMJEvents = mysql_num_rows($rsEMJEvents);

Thanks

Winrol

Report · Apr 05, 2011

The query code looks accurate to me.

winrol wrote:
When I was editing it in the record set dialog box when I had the first filter in, speaker, then tested it and had to give a name then I got all the records with the speaker name. I had to go to the advanced tab and added the second filter when I tested it nothing came up to ask for a name and date and I got nothing and there should be 1 record there

when testing your pages with a real browser, do you get the same result ?

Report · Apr 05, 2011

Hi I was wondering if the following code is correct?

$colname_rsEMJEvents = "-1";

if (isset($_POST['speaker'])) {

$colname_rsEMJEvents = $_POST['speaker'];

}

$colname2_rsEMJEvents = "-1";

if (isset($_POST['date'])) {

$colname2_rsEMJEvents = $_POST['date'];

}

Should it be $_GET['speaker'] and $_GET['date'] instead of POST?

When I run the code in a browser and select a speaker and enter a date then hit the submit button nothing happens. Now I believe nothing happens because I am not sure what to put in the *action* of the form.

Would it be the function:

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

Once the query has ran how do I get the results from it. Is it in one of the following variables? $row_rsAvailability, $totalRows_rsAvailability?

$rsAvailability = mysql_query($query_rsAvailability, $connEMJEvents) or die(mysql_error());

$row_rsAvailability = mysql_fetch_assoc($rsAvailability);

$totalRows_rsAvailability = mysql_num_rows($rsAvailability);

What my query is supposed to do is check if the "Speaker" is booked on the "Date" input. Then if he is not booked display in a *textarea* that the "Speaker" is available on that "Date".

Thanks

Winrol

Report · Apr 06, 2011

winrol wrote:
Should it be $_GET['speaker'] and $_GET['date'] instead of POST?

Only if the form in question transfers the data via the GET method.

Now I believe nothing happens because I am not sure what to put in the *action* of the form.

Assuming that "page1.php" contains the form and "page2.php" contains the Recordset, the form´s "action" is page2.php.

What my query is supposed to do is check if the "Speaker" is booked on the "Date" input. Then if he is not booked display in a *textarea* that the "Speaker" is available on that "Date".

Let´s address this question after getting the main issue sorted out.

Report · Apr 06, 2011

This might help on what I am looking to do.

This is my template page and I have 2 recordsets set up. rsCurEvents and rsBookings. I want the rsBookings to check and see if the selected speaker is booked on the entered date and then change the text in the textarea box. The other record set rdCurEvents works. When I select Current Events from the menu the page opens up and shows me all the info. from the rsCurEvents in a table.

So page1, my template, contains the form and recordset.

Thanks

Winrol

Report · Apr 06, 2011

winrol wrote:
So page1, my template, contains the form and recordset.

So the form is posting its values to the same page then ? In this case the form´s "action" will be different and needs to point to the current document: <?php echo $_SERVER['PHP_SELF']; ?>

Thanks for providing a visual representation of your form. I think you´re going to run into some unexpected trouble with this, because...

a) what will happen when the visitor enters a wrong date format (say, dd-mm-yyyy) or nothing at all or just some weird stuff because "he can" ? Your query will utterly break, because I don´t think you´re currently providing for a much-needed validation.

b) in case the visitor doesn´t select any speaker at all, the query will break too, because right now the query defines that both form elements have to submit some value for returning a result. At least in this case you can avoid some trouble by doing away with the default "select a speaker" option (which certainly provides an empty value) and have the menu display the list of speakers only.

Report · Apr 06, 2011

My current document is "index.dwt.php".

So my form action would be:

<?php echo $_SERVER['PHP_SELF']; ?>

not

<?php echo $_SERVER['index.dwt.php']; ?>

Here are a couple more screen shots.

If the date is not entered " yyyy-mm-dd " or there is none entered or if no speaker is selected then my validation pops up requesting a valid date or value is needed and if no speaker is selected you need to select one.

I have also set up a couple of "textareas" which I have "Show if Recordset, rsBookings, is empty" and one that says "Show if Recordset, rsBookings, is not empty.

When my page first comes up the top box is visible and the bottom one is not.

I have updated my form's "Acton" to <?php echo $_SERVER['PHP_SELF']; ?> and entered a date and a speaker and hit the submit button. And nothing happens. I know that the speaker I have selected should not be available on the selected date so the top box should hide and the bottom one show.

How do I get the form to "refresh" without loading the page again and showing the correct box.

Thanks

WInrol

Report · Apr 06, 2011

winrol wrote:
Here are a couple more screen shots.

If the date is not entered " yyyy-mm-dd " or there is none entered or if no speaker is selected then my validation pops up requesting a valid date or value is needed and if no speaker is selected you need to select one.

And what if some visitor has javascript disabled in the browser settings ? Let´s make no mistake about it :: you need to implement a server-side validation in any case, the Spry-based javascript validation is just an unreliable bonus.

I have updated my form's "Acton" to <?php echo $_SERVER['PHP_SELF']; ?> and entered a date and a speaker and hit the submit button. And nothing happens.

It´s hard to judge why exactly this happens, and at this stage I think that you should consider having some expert look over your shoulder and get things sorted on location.

Report · Apr 05, 2011

As an aside, and probably nothing to do with your problem - 'date' is a reserved word in many dbms's and is best avoided as it leads to complications. Use something more meaningful, like bookedDate or availableDate.