Basics of extracting data from MySQL using PHP

selecting rows using submitted form data

&nbsp

expert_21

5:30 pm on Oct 31, 2003 (gmt 0)

I'm a beginner in php/mysql, and i recently tried to mess with them using dreamweaver. I need a php script in which user could select a row from a table(through a form) and then have the script display them. How do i do that in dreamweaver? Below is what i've started:

<removed dw code> - jatar_k

would be extremely grateful if someone can lend a helping hand. thanks in advance.

expert_21

actually, i would prefer passing var to URL but to be honest, i have no idea how that can be done, and i know it would be frustrating for you guys to walk me through the whole process.

It seems a user can enter an id and manufacturer then select other data from the db and it will be displayed by ouraction.php.

yes, that's right. my logic was to make the form pass the necessary variables (say ID) to the script when submit, and then the script displays it's corresponding row. so once i set up the form like above, what's next? i should ask the script to query the database? sorry, absolutely a beginner.

jatar_k

11:16 pm on Oct 31, 2003 (gmt 0)

a very good description of the situation, more than enough to start.

so if the form above is in ourform.php we can now create a seperate script called ouraction.php to do the processing. I don't process in the same script as the form. Helps stop double posting and other weird problems that may arise.

basic structure of ouraction.php

1. connect to mysql 2. select the db 3. build our mysql query from form values 4. retrieve the info from mysql 5. display, if it is there

required reading PHP MySQL Functions [ca.php.net] - all the functions below are listed here PHP Predefined Variables [ca.php.net] - for looking at $_POST and $_GET arrays necessary for accessing data sent from a form

so we are going to connect to mysql and store the returned link identifier in the variable $connection by passing the host (in this case localhost), username and password, which are stored in variables, to the mysql_connect function. The last part specifies that is it fails stop the script and show me the error number and text description of the error returned from mysql.

I use both the error number and text for my die statement. In case the error description is vague I can always search it on my search engine of choice.

2. select the db

assuming we are now connected to mysql we need to tell it what database to use.

tell mysql to use the database name stored in $dbname. You will notice that the link_identifier is an optional parameter. The mysql functions always assume you mean for them to use the last opened identifier. You only need to pass the link_identifier if you have more than one connection open for any given script.

3. build our mysql query from form values

As far as queries go you will have to familiarize yourself with mysql but we are doing something simple here so you can go to [mysql.com...] and read to your heart's content later.

I always build my queries before I pass them to the mysql function. It gives me more options for debugging bad queries. We now need to do a select query using the two fields that were entered into our form.

Our form method was set to post so we will access the values using the "name" of our input fields in the $_POST array.

since we are trying to learn something here I use echo to show the contents of the variable. Seeing it in full will help you understand how it was built. The period between some parts of the string is the concatenation operator. We are essentially gluing all the parts together into the variable to create a long string as you will see when you echo the contents of the variable.

4. retrieve the info from mysql

We need to fire the query at mysql.

resource mysql_query ( string query [, resource link_identifier])

this returns a resource so we need to have it go to a variable.

$query = mysql_query($sql);

5. display, if it is there

now we need to retrieve the information from the resource. There may, or may not, be any data there. How this part is handled really depends on how the search is built. You can limit the search to only search existing manufacturers. For the sake of simplicity we will assume that we found the manufacturer we were looking for.

So this is a little more difficult. A while loop will execute as long as the statement is true. Therefore, as long as there is a row available it will do whatever is between the { }. In this case we may have returned more than one row. If we return 5 rows then it will loop 5 times and show 5 ids and manufacturers. Each time we call mysql_fetch_array it will grab one row and then we will process that line and go back to the top and do it again for the next row.

$row will be an array that is why we need to access the values in the array with $row['id']. We are looking for the value from the id column or from the manufacturer column. We then just echo them to the browser. You will probably want to format them better than I did.

You also can see that for echo I used commas between the different parts not the dot. You can use the dot but then echo has to put them all together before it can spit them out. This way it just spits the pieces out as it goes and is much faster.