Known Participant

Question

Database queries

Forum|Forum|15 years ago
April 4, 2011
1 reply
2885 views

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

Server side applications

This topic has been closed for replies.

Günter_Schenk

Inspiring

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".

W

winrolAuthor

Known Participant

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

Günter_Schenk

Inspiring

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 ;-)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded