Got a Resource id #5 appearing, don’t worry!

So I was echoing out my $query and my $result variables and I coded my site in order to check my SQL statements and I came across a value of ‘Resource id #5’ in my echo values where I instead expected to see a customerID.

The answer? Well,  you can’t echo the result of a sql query,   i.e you can’t do this:

$query = "SELECT CustomerID  FROM CUSTOMER WHERE emailAddress = '".$emailAddress."'";
$result=mysql_query($query);
echo $result;

instead you need to identify which item in $result you want to echo (and for which row). In my instance the item was CustomerID and  I thought it would be good to iterate through all the rows in the resultset, so I echoed it like this:

$query = "SELECT CustomerID  FROM CUSTOMER WHERE emailAddress = '".$emailAddress."'";
$result=mysql_query($query);

//Get the number of rows in the recordset
$num=mysql_numrows($result);

//Set the initial value of the counter to zero (the first position in the resultset)
$i=0;

//iterate through the resulset and echo the value of the CustomerID for each record
while ($i < $num) {
echo mysql_result($result,$i,"CustomerID");
$i++;
	}

It’s an easy mistake to make, but a bit thoughtless when you think about it..after all, how can we expect to echo a recordset? I don’t think I’ll be making that mistake again!!

Advertisements

How to post the values of set checkboxes back to the database – Stage 5 complete!

Much play has stopped work for a number of days, but tonight I picked up my laptop and went back to the problem that I had in passing the values of the checkboxes that the user had checked back to the database.

Why on earths name I thought I might need to use javascript I have no idea…too much internet reading of the wrong thing I think.

Anyway, here’s how I managed to solve the problem of presenting the list of items from the database with checkboxes and then updating the database with values of the checked checkboxes. The first part gets the items from the database and displays them on the page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Diplay data as checkboxes</title>
</head>

<body>

<?php
// Include connection to my local server
include("Connections/connectionData.php");
//Connect to the database - 
mysql_connect ($server, $username, $password, $dbName) or die ('Error: ' .mysql_error());
mysql_select_db($dbName);
//Create the query
$query="SELECT * FROM TableName WHERE columnName = 1";
//Define the variable that will hold the result set
$result=mysql_query($query);
//Find out how many records there are in the resultset
$num=mysql_numrows($result);
?>

<!-- Create the form - this will tell the page which php script to get when the form is submitted --->
<form action="process_changes.php" method="post">

<!-- go into php scripting to obtain each value individually from the resultset --->
<?php
//Set up the loop to loop thru the result set
$i=0;
while ($i < $num) {
	
//set the value from the current row of the resultset to the $field1 variable
$field1=mysql_result($result,$i,"myColumnName");
?>


<!-- Display the checkbox on the page containing the value from the current row of the resultset --->
  <input type="checkbox" name="items[]" value="<?php echo $field1;?>" /> <?php echo $field1;?><br />

<!-- break back into php to increment the variable - and therefore go to the next row of the resultset -->
<?php
	
	//increment the counter
	$i++;
}


?>

<!-- Place a submit button on the webpage, so that the checkbox values selected are passed when the button is clicked -->
  <input type="submit" value="Submit" />
</form>


</body>
</html>

The second part is the script process_changes.php (which is called from the previous script). This script gets the items array (which holds the list of checkbox values) from previous page and builds an SQL INSERT statement for each item. This may not be the way that you want to send your inserts to the database, but it gives you an idea anyway.

<?php

$checkBox = $_POST['items'];
for($i=0; $i<sizeof($checkBox); $i++){
    $query = "INSERT INTO table(TableToInsertInto) values('".$checkBox[$i]."')";
//	echo $query;
   mysql_query($query) or die(mysql_error());
}

?>

So Stage 5 is now complete – I now just need to collect some more data from my user and write some more update and insert queries and then I get to work on Stage 7, which is finding out how to send a verification e-mail to a user and then process it’s return. I expect that this will be pretty easy since it’s the corner stone of a lot of web actions. I’m more than 25% of the way through now.  It’s great to be doing something and not just thinking about it.

Can I use javascript?

I’m stuck at Stage 5.

I have a mySQL table which contains a list of items. I get this list of items and I display each item in its own row in the first column of a two column table on my webpage. In the second column of each row I have a checkbox. I want the user to select the items which apply to them and then submit the page using the submit button. I cannot work out how to do this without using a javascript event handler ie. an event handler which kicks off some code when the submit button is clicked.

From my limited reading so far, I understand that javascript is often disabled in browsers and also that its use is not very secure, but since I can’t for the life of me work out how I can use a server side script to determine whether something has happened on the client side. I even tried writing  while statement to wait until the submit button was set, but ofcourse that executed on the server, so it just went into an infinite loop.

Is there anyway to do this without using javascript?

Anyway, it’s Easter Sunday, and I think a need a break, Im sure the answer is very simple…I’m off to deliver Easter Eggs. Happy Easter!!

Stages or milestones in my journey

In my last post I stated that stage 4 was complete.  So I thought I’d share with you what I consider my stages to be:

Stage 1  – Configure my environment so that I have local server and choose an IDE – done

Stage 2 – Design my database schema and implement it using mySQL – done

Stage 3 – Populate the reference data table(s) – done

Stage 4 – Create a web page which queries my database and returns data displayed as page content – done

Stage 5 – Make updates to the dynamically generated data and write it back to the database

Stage 6 – Iterate through stages 5 and 6 until I have all the data that I need being captured and written back

Stage 7 – Work out how to trigger sending an e-mail based on a database update

Stage 8 – Work out to create an e-mail using content from my database

Stage 9 – Add validation (not sure how yet) to the data being captured

Stage 10 – Read about security..check my code…I have a book on common hacks etc so that will be a start, then extensive web reading and code fixing

Stage 11 – Try to understand how to format my web pages using css

Stage 12 – Make some funky images, icons, logos

Stage 13 – Test the whole thing on my local webserver

Stage 14 – Put the whole thing on an externally hosted environment

Stage 15 – Test it again now that it’s been ported to a new environment <I’m a tester by trade, so finding bugs will be easy, but the fixing will be a nightmare>

Stage 16 – Inform everyone I know and ask them to beta test it

Stage 17 – Learn about SEO and apply my learning to my site

Stage 18 – Put the site live

Stage 19 – Do some marketing

Stage 20 – Monitor the site

We have reference data and a page with dynamic content

My reference data table is now loaded and I have now managed to get the data from my database and out onto the lovely php page, I have dymamically generated content!

I think I can say that stage four is now complete.

Oh I found out today that sometimes, if you don’t restart your webserver after making code changes, the code hangs onto old values in some it’s variables, so now I’m doing a webserver restart after every major update to the code…I’m nots sure yet if this is normal, or if it’s just my code that’s bad…I need to do more reading a learn a bit more before I figure this out.

The easy part – mySQL

If there is one part of this that I’ve found easy it’s the creation of my database. I can’t believe that mySQL & the tool mySQL Workbench is free. For anyone who is used to creating databases with something like MS SQL Server it is cinch to move across too. Although I personally find it easier to hand code my DDL for a database, the design tool does look useful for anyone new to SQL. I found that it was useful to be able to reverse engineer my database using the design tool so that I could just do a quick visual check to make sure that I had implemented the correct foreign keys.

There are certainly syntax differences between MS SQL Server and mySQL, but nothing that a quick lookup of the mySQL manual doesn’t fix in a few seconds. I’ve yet to write a stored procedure in mySQL and it might get more difficult when I’m trying to use T-SQL everywhere, but I’m sure there’s a T-SQL to mySQL crib sheet out there on the internet somewhere.

By the way, it took my a while to work out that command + return executes only the highlighted statement….I was cursing the inability to execute selected statements for a few hours then I realised that there were two execute buttons. Just thought I’d mention it case anyone else who had moved from SQL Server Management Studio was as dumb as me.

Where on my mac is my php.ini file?

I found the path to it by creating a .php script called phpinfo.php with the following code in it:

<?php

phpinfo()

?>

I then went to my browser navigated to the  http://127.0.0.1/useful/phpinfo.php and hey presto, the full current configuration was displayed in the browser including the php.ini path. (it was in /etc)

Warning: mysql_connect() [function.mysql-connect]: No such file or directory

I spent an age trying to work out why I couldn’t post a simple e-mail address to a PHP database. Everytime I tried to test submitting the form using my local server I got the following message:

Warning: mysql_connect() [function.mysql-connect]: No such file or directory

I was using the following connection string:

mysql_connect ("localhost",  "root", "xxxx", "my_fdb") or die ('Error: ' .mysql_error());

It turns out that when I changed “localhost” to the actual ip address and port of my local mySQL database, the error disapeared:

mysql_connect ("127.0.0.1:3306",  "root", "xxxx", "my_fdb") or die ('Error: ' .mysql_error());

Now if I was on a windows machine I would have known where to go to edit my locahosts file, but on a mac I haven’t the first clue and right now I don’t care, I’m just pleased that I’m now able to write to and read from my local database.

Dreamweaver

Well, in the end I decided to give Dreamweaver a whirl as my IDE, the thought of using text editors and having to manipulate everything manually was just to much. I think that if I watch the adobe tutorials and then just go for it I will probably learn the basics of getting a site which uses a database quite quickly…ok, I’ll only learn what I need, but at least I’ll have a basis to build on and have achieved something. Anyway, learning has to be easier having the code side by side with a design view.

So far I’ve learned the purpose of a style sheet; I’m still not sure what XHTML is; I’ve used

tags and now I’m trying to work out how to install/ configure a mySql database (thankfully SQL is a skill that I already have).

I’m really looking forward to the point where I have written some values from my form to my database.

I hope to get some time on Tuesday night to get the db set up, hopefully i’ learn a few useful tips that I can record here for posterity!

Which Development Environment?

I’ve been trying to find out which IDE I should use to start on my journey.

(Why does this wordpress do double line spacing?)

In my google search for IDE’s on a Mac I came across the blog of a guy called Sean Barton (http://www.sean-barton.co.uk/2009/02/setting-up-a-phpmysql-local-development-environment-on-a-mac-doing-it-properly/) and thought, that looks good, I’ll learn how to set up a php and mysql development environment on my mac. So, I followed his instructions and downloaded and installed mysql then configured apache to load the php module (I think this is the right terminology!).

The big question is, was it simple? The answer is, not as simple as I thought it would be. Below are the things that I learned along the way…they might be useful for another complete newbie.

Tip1: It’s the MySQL Community Server edition that you want to download ( I hope)

Tip 2: Make sure you use a mirror from the mysql site to get a .dmg file and not a .tar file.

Tip 3: To a copy a previous command in a Terminal window use crtl+P. For a full list of commands see this helpful bash cheat sheet

Tip 4:  How do you edit a file using vi? Answer: I”ve no idea – I used nano [to do this I substitued the word pico for the word vi when I executed this command ]

sudo vi /private/etc/apache2/httpd.conf

Tip 5: How to edit a file using nano? Answer : Wherever you see the ^ sign means hold the crtl key! – This site contains a neat crib sheet.

Tip 6: apachectl is not the same as apachrct1 – be careful of 1s and ls when looking at text which uses a terminal style font

Tip 7:  The command sudo /usr/sbin/apachectl restart doesn’t work. It errors with:

usr/sbin/apachectl:
line 82: ulimit: open files: cannot modify limit: Invalid argument

The guy at this deversus page explain a way around it, but to be honest I just ignored it and continued.

Anyway, after removing the index.html page from the /Library/WebServer/Documents folder and navigating to 127.0.0.1 in my browser and pressing refresh a few times,  I finally saw a page come up containing a question mark on it…so I guess my environment is now working!!

So, lots of new things learned today. Roll on tomorrow when I’ll be working out what I should do next!