[Foreign Keys]

Hi,

 

whilts on the subject of foreign keys:I am currently working through the review and persue section of chapter 10, creating delete and edit scripts for the banking database. This database has foreign key constraints (ON DELETE NO ACTON). The database is made up of at least 2 tables (an accounts table [child] and the customers table [parent])

 

To delete a customer I first have to delete the child [account] that is clear. Question is, when writing my PHP script do I have to do this using 2 separate queries (i.e. DELETE FROM accounts WHERE customer_id=1 ...run that and then the same for the parent table) or is there a way to run this using just 1 query.

 

Thanks

LF

[1St & 2Nd Normal Forms...Sanity Check]

hi,

 

thanky you both very much for this. It goes a long way into helping me fully understand joins.

 

thanks again

LF

[1St & 2Nd Normal Forms...Sanity Check]

OK....next question. I am struggling with INNER and OUTER JOINS. How would I write a query to tell me what member of staff speaks which langauge?

 

So far I have come up with:

 

SELECT s.first_name, s.last_name

FROM staff AS s

INNER JOIN staff_lang

USING ( staff_id );

 

This will just link the "staff" table with the link table "staff_lang" containing the staff_id and lang_id foreign keys. Could anybody give me a tip on how to link the "lang"-->"staff_lang"<---"staff" tables to display the results?

 

Sorry for posting such a basic question, but I am really struggling with this.

 

Thanks,

LF

[1St & 2Nd Normal Forms...Sanity Check]

Thanks Antonio. I thought I had it, but just wanted to check as I have checked several references for a normalization definition, but wasn't sure I had got it right.

[Spam Scrubber Function Q]

Hi,

 

i have used the spam_scrubber function on a form in which I have included a drop down list and radio buttons.

 

When I use the spam_scrubber function to validate the other text areas, that it works fine, however once I select something I get a the following warnning message:

 

Warning: stripos() expects parameter 1 to be string, array given in file name on line 18.

 

line 18 is :

f(stripos($value, $v)!==false)

)

However I think the error is generated from the line

 

$scrubbed=array_map('spam_scrubber', $_POST);

 

i.e. the $value has to be a string for the function to work and since my select box returns an array, there is a problem, hence the error message.

 

Is there a way around this,other than using a @ in front of the line above?

 

Thanks,

LF

[1St & 2Nd Normal Forms...Sanity Check]

Hi Hartley San,

 

thank you for your comments. It is good to bounce ideas off you experts to make sure that I am on the right track.

 

Thanks you again!

LutinFou

[1St & 2Nd Normal Forms...Sanity Check]

Hi!

 

This is not a question as such, I am mainly looking for somebody to tell me whether I am on the right track or way off the mark.

 

I am currently working through the MySQL chapters, in particular Normalization. i just want to check if I have understood everything corrctly so far.

Say I am creating a "language skills database" for an office, where all of the staff speak one or more foreign languages.

 

Staff_id (PRIMARY KEY)

first_name

last_name

email

telephone number

start_date

language

 

The way I understand normalization, I would need to first create table for the staff, removing the language column as each member of staff my speak more than one foreign language (French, German and Spanish for instance) which would be in violation of 1NF (i.e. atomicity) ie.:

 

Staff_id (PRIMARY KEY)

first_name

last_name

start_date

 

This table would be in 1NF, as each entry has a Primary key, each field is atomic and each record will appear only once.

 

I would then have to create a "Language Skills" , i.e.

 

lang_id (PRIMARY KEY)

language

 

Again, I understand that this table would be 1NF.

 

The Relationship between the two tables is currently M:M i.e. one memeber of stafff can speak mutiple languages, and each language (French for instance) may be spoken by many members of staff.

 

I would therefore require a thrid table (a link / intermediary table) along the following lines:

 

Staff_id (FOREIGN KEY)

lang_id (FOREIGN KEY)

 

 

whereby the link from the staff table to the link table is 1:M and the link from the Languages table to the link table is also 1:M

 

So then by now my database is 1NF (no listed fields, and each table has a primary key) and 2NF (any columns that would contain duplicated data - ie. the languages spoken by the staff - have been slipt off into a new table - i.e. the languages table)

 

Does this sound about right?

 

 

All comments and tips will be very much appreciated.

Thanks

LF

[Sticky Foms & Html Select List Generated By Php]

Hi all,

 

many many thanks to all of you for your help.

 

the "in_array" suggestion form Paul does the trick. In fact after re-reading the section on arrays last night, I realized that the $_POST['selService'] was an array value. I am just was not advanced enough in PHP to know how to to call (I do this all the time - get too far ahead of myself, although I guess that is the way to learn!)

 

Now just got to wait for all the hair I have pulled out over this little problem to grow back hehehehe!

 

Thanks again to you all

Jonathan

[Sticky Foms & Html Select List Generated By Php]

Hi,

 

 

thanks for all of your help.I would never have figured out those bits of code myself.

For some reason, the form is still not sticky. Guess I can live with out it for now )

 

thanks again!

Jonathan

[Sticky Foms & Html Select List Generated By Php]

Hi Jonathon,

 

thanks for your help. I understand what you mean, I just seem to be having troble implementing it.

 

I have changed the script somewhat to:

echo

 

'<select class="formInputText"name="selService[]"id="selService"multiple="multiple">';

 

 

$clientOption=$_POST['selService'];

 

 

foreach($options as $service){

 

 

echo"<option value=\"$service\" if(isset($clientOption)&&($clientOption==$service))echo'selected=\"Selected\"';>$service</option>\n";

}

 

however the line

if(isset($clientOption)&&($clientOption==$service))echo'selected=\"Selected\"';>

 

 

 

is treated as HTML and not PHP on the generated HTML, ie:

 

<select class="formInputText"name="selService[]"id="selService"multiple="multiple"><option value="Architecture" if(isset()&&(==Architecture))echo'selected="Selected"';>Architecture</option>

<option value="Interior Design" if(isset()&&(==Interior Design))echo'selected="Selected"';>Interior Design</option>

<option value="Dimensional Surveys" if(isset()&&(==Dimensional Surveys))echo'selected="Selected"';>Dimensional Surveys</option>

<option value="Party Wall Surveyors" if(isset()&&(==Party Wall Surveyors))echo'selected="Selected"';>Party Wall Surveyors</option>

<option value="In-house visualizations" if(isset()&&(==In-house visualizations))echo'selected="Selected"';>In-house visualizations</option>

<option value="CDM" if(isset()&&(==CDM))echo'selected="Selected"';>CDM</option>

</select>

 

any ideas where i am going wrong?

 

 

thanks

Jonathan

[Sticky Foms & Html Select List Generated By Php]

Hi,

 

by using the code:

 

//Array containing option values

 

 

$options=array('Architecture','Interior Design','Dimensional Surveys','Party Wall Surveyors','In-house visualizations','CDM');

 

//Foreach loop to insert values into HTML

 

echo'<select class="formInputText"name="selService[]"id="selService"multiple="multiple">';

 

 

foreach($options as $service){

 

 

echo"<option value=\"$service\" if(isset($_POST[$service])&&($_POST[$service]==$service))echo 'selected=\"Selected\"';>$service</option>\n";

}

 

echo'</select>';

 

?>

 

The HTML gernerated reads:

<select class="formInputText"name="selService[]"id="selService"multiple="multiple"><br />

<b>Notice</b>: Undefined index: Architecture in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<br />

<b>Notice</b>: Undefined index: Architecture in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<option value="Architecture" if(isset()&&(==Architecture))echo 'selected="Selected"';>Architecture</option>

<br />

<b>Notice</b>: Undefined index: Interior Design in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<br />

<b>Notice</b>: Undefined index: Interior Design in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<option value="Interior Design" if(isset()&&(==Interior Design))echo 'selected="Selected"';>Interior Design</option>

<br />

<b>Notice</b>: Undefined index: Dimensional Surveys in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<br />

<b>Notice</b>: Undefined index: Dimensional Surveys in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<option value="Dimensional Surveys" if(isset()&&(==Dimensional Surveys))echo 'selected="Selected"';>Dimensional Surveys</option>

<br />

<b>Notice</b>: Undefined index: Party Wall Surveyors in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<br />

<b>Notice</b>: Undefined index: Party Wall Surveyors in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<option value="Party Wall Surveyors" if(isset()&&(==Party Wall Surveyors))echo 'selected="Selected"';>Party Wall Surveyors</option>

<br />

<b>Notice</b>: Undefined index: In-house visualizations in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<br />

<b>Notice</b>: Undefined index: In-house visualizations in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<option value="In-house visualizations" if(isset()&&(==In-house visualizations))echo 'selected="Selected"';>In-house visualizations</option>

<br />

<b>Notice</b>: Undefined index: CDM in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<br />

<b>Notice</b>: Undefined index: CDM in <b>C:\xampp\htdocs\HoltArchitects\pages\contact_holt_architects.php</b> on line <b>67</b><br />

<option value="CDM" if(isset()&&(==CDM))echo 'selected="Selected"';>CDM</option>

</select>

 

The php I am using to validate the select box is:

 

//Select box validation

 

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

 

 

 

$mySelection=count($_POST['selService']);

 

 

 

echo'<p>';

 

 

for($i=0;$i<$mySelection;$i++){

 

 

echo $_POST['selService'][$i]."<br /> ";

}

 

 

 

echo"</p>";

}

 

 

else{

 

 

$_POST['selService']=NULL;

 

 

echo'<p>Please select a service</p>';

}

 

 

thanks,

Jonathan

[Sticky Foms & Html Select List Generated By Php]

Hi,

 

first of all....this is a great book!

 

Ok now to my question. I have created a HTML form in which there is a selection box generated by a php foreach loop:

 

<?php

//Array containing option values

$options=array('Architecture','Interior Design','Dimensional Surveys','Party Wall Surveyors','In-house visualizations','CDM');

//Foreach loop to insert values into HTML

echo'<select class="formInputText"name="selService[]"id="selService"multiple="multiple">';

foreach($options as $service){

echo"<option value=\"$service\">$service</option>\n";

}

//close HTML select tag

echo'</select>';

?>

 

If I were to make a sticky form using a conventionally generated HTML select box (i.e. typr it all out myself rather than let php do it, I would do something like the following:

 

<option value="Architect"><?php if(isset($_POST['Architect'])echo 'selected="selected"?>Architect</option>

 

In the form I have created with php, I was thinking that the following would work:

 

echo"<option value=\"$service\">if(isset($_POST[$service]))echo\'selected\'="\selected"\</option>\n";

 

but it doesn't. I have tried several variations of this (i.e. with single and double quotes, etc...) but to no avail.

 

Would you be able to point me in the right direction?

 

Thanks

Jonathan

 

 

 

 

?>

[Problem With Chapter 2 Handling Php Form]

Just been reading your post. Re the text editor, I use Aptana Studio 3 (open source) it is great at spotting my typing errors