homepage Welcome to WebmasterWorld Guest from 54.227.182.191
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Basics of extracting data from MySQL using PHP
selecting rows using submitted form data
expert_21




msg:1293521
 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.

[edited by: jatar_k at 11:25 pm (utc) on Nov. 2, 2003]
[edit reason] removed dw php code since we didn't use it [/edit]

 

jatar_k




msg:1293522
 5:50 pm on Oct 31, 2003 (gmt 0)

alright lets work through this and forget about DW, that code is ugly (not your fault at all). So this is the form

<form name="form1" method="POST" action="ouraction.php">
<p> id: <input name="id" type="text" id="id">
<p> manufacturer: <input name="manufacturer" type="text" id="manufacturer">
<p><input type="submit" name="submit" value="submit">
</form>

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.

that right?
and what version of php are you using?

As you can see, I am not going to just give you the code. Maybe we can do a little learning together. :)

lorax




msg:1293523
 7:24 pm on Oct 31, 2003 (gmt 0)

>> lets work through this and forget about DW, that code is ugly

That's lesson #1 ;)

I've used something similar quite often but I don't use a form. I turn the rows into links and place any necessary vars on the URL.

But don't let an alternate solution distract you. Work through the first one and then you'll see the advantages and disadvantages yourself.

expert_21




msg:1293524
 9:56 pm on Oct 31, 2003 (gmt 0)

my php is version 4.3.3. Sure i'm glad someone is willing to teach me!

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




msg:1293525
 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

1. connect to mysql
resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]])

this function returns a link identifier to mysql that we can then use to communicate with our database. If this function bombs, it's all over because we can't talk to the db.

$host = "localhost";
$user = "mysqlusername";
$pass = "mysqlpassword";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");

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.

bool mysql_select_db ( string database_name [, resource link_identifier])

$dbname = "ourdatabase";
mysql_select_db($dbname);

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.

$sql = "select * from ourtablename where id=" . $_POST['manufacturer'] . " and manufacturer='" . $_POST['manufacturer'] . "'";
echo $sql;

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.

array mysql_fetch_array ( resource result [, int result_type])

while ($row = mysql_fetch_array($query)) { 
echo "<p>",$row['id'],": ",$row['manufacturer'];
}

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.

So that leaves us with this code for our script

<?
$host = "localhost";
$user = "mysqlusername";
$pass = "mysqlpassword";
$dbname = "ourdatabase";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);

$sql = "select * from ourtablename where id=" . $_POST['manufacturer'] . " and manufacturer='" . $_POST['manufacturer'] . "'";
echo $sql;

$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)) { 
echo "<p>",$row['id'],": ",$row['manufacturer'];
}

?>

You don't need to close the connection at the end because it is automatically closed when the script finishes.

a few more links
while [ca.php.net]
echo [ca.php.net]
String Operators [ca.php.net]

This will give you something to work with.
Questions?

[edited by: jatar_k at 8:48 pm (utc) on May 21, 2004]

lorax




msg:1293526
 4:15 am on Nov 1, 2003 (gmt 0)

bravo perfessor ;)

Should pin this to the top of the forum - would help some other folks as well I'm sure!

ergophobe




msg:1293527
 6:08 pm on Nov 2, 2003 (gmt 0)

expert_21 - luck you didn't have to pay consulting fees for that one!

Great job JK. May I suggest that this be retitle and made into a library topic? It would be a worthy addition.

Tom

coopster




msg:1293528
 1:13 am on Nov 3, 2003 (gmt 0)

ditto. Flagged this one for future reference to others. Nice work jatar_k, we appreciate it.

expert_21




msg:1293529
 9:57 pm on Nov 5, 2003 (gmt 0)

thanks jatar_k, for that long tutorial...however, i'm still stuck when i try using it with 4images. :(

Here's the form:


<form name="form1" method="post" action="results.php">
<p>Select Name
<select name="user" id="user">
<option value="1">Jane</option>
<option value="2">Patricia</option>
<option value="3">Patrick</option>
<option value="4">Peter</option>
</select>
</p>
<p>
<input type="submit" name="Submit" value="Submit">
</p>
</form>

and here's the php:


<?
$host = "localhost";
$user = "user";
$pass = "password";
$dbname = "database";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname)

$query= "select * from 4images_images where user_id=" . $_POST['user'] . "";
echo $query;

$result= mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "username: ", $row['user_id'], ": ",$row['image_name'];
}

?>

It says "Parse error: parse error in /home/.../results.php on line 18"

and line 18 is:

$query= "select * from 4images_images where user_id=" . $_POST['user'] . "";

What am i doing wrong? sorry for being such a nitwit. :(

jatar_k




msg:1293530
 10:05 pm on Nov 5, 2003 (gmt 0)

no such thing as a nitwit around here

mysql_select_db($dbname);

the parse error drops through to the next line with out the semi colon on line 17 (or 16, whichever)

and yes, we have all done this many times. Sometimes you stare at code for so long you just need an extra pair of eyes. :)

expert_21




msg:1293531
 10:24 pm on Nov 5, 2003 (gmt 0)

actually, this is my first time doing this :)

alright, i've put in the ; sign @ the end, but now i get another error. After pressing the 'submmit' button, i get this error:


select * from 4images_images where user_id='
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/mobile92/public_html/results.php on line 21

how is it that the page displays the php codes itself? arrhhhh...i'm spending like an hour with this script. anyone can do any worse? :)

expert_21




msg:1293532
 10:28 pm on Nov 5, 2003 (gmt 0)

opss, and line 21 is:

$num_results = mysql_num_rows($result);

i'm wondering if this line is wrong:

$query= "select * from 4images_images where user_id=" . $_POST['user'] . "";

is it missing a ' @ the end of the line? like should i put

$query= "select * from 4images_images where user_id=" . $_POST['user'] . "'";

coopster




msg:1293533
 11:06 pm on Nov 5, 2003 (gmt 0)

Right on, but you need the quotation marks before and after your string:
$query= "select * from 4images_images where user_id='" . $_POST['user'] . "'";

Have a look at this thread for more tips and discussion on string and date values needing to be specified as quoted strings:
[webmasterworld.com...]

expert_21




msg:1293534
 12:24 am on Nov 6, 2003 (gmt 0)

it worked! thank you all esp. jatar_k for your magnificient tutorial, and coopster for pointing out the error! This is my first php script. Thank you very much!

expert_21




msg:1293535
 1:02 am on Nov 6, 2003 (gmt 0)

hey, sorry, i'm back. :) i'm just wondering if i want to have a total score kind of thing. so i tried this:


<?php for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<tr>";
echo "<td>", "Image Name: ", $row['image_name'], "</td>";
echo "<td>", "Hits: ", $row['image_hits'], "</td>";
echo "<td>", "Downloads:", $row['image_downloads'], "</td>";
echo "</tr>";
$totalhits=totalhits + $row['image_hits'];
}
echo $totalhits;
?>

but then it turns out $totalhits = the last hit recorded, and not the overall hits? does $totalhits get cleared everytime the loop is repeated?

expert_21




msg:1293536
 1:04 am on Nov 6, 2003 (gmt 0)

ok..another syntax error:

$totalhits=totalhits + $row['image_hits'];

should be

$totalhits=$totalhits + $row['image_hits'];

:)

jatar_k




msg:1293537
 2:09 am on Nov 6, 2003 (gmt 0)

also a piece of shorthand

$totalhits=$totalhits + $row['image_hits'];

is the same as

$totalhits += $row['image_hits'];

you can use this anytime you are doing arithmetic functions with the same var

$totalhits=$totalhits - $row['image_hits'];
same as
$totalhits -= $row['image_hits'];

$totalhits=$totalhits * $row['image_hits'];
same as
$totalhits *= $row['image_hits'];

$totalhits=$totalhits / $row['image_hits'];
same as
$totalhits /= $row['image_hits'];

expert_21




msg:1293538
 9:58 am on Nov 6, 2003 (gmt 0)

thanks!
right, now i'm trying to 'enhance' a bit. :) I want to change the color of the font.

echo "<td>", $row['image_rating'], "</td>";

hence i put this:

echo "<td><font color="#FFFFFF">", $row['image_rating'], "</font></td>";

but it didn't work? is it because of the " sign that font color has? thank you in advance.

coopster




msg:1293539
 12:11 pm on Nov 6, 2003 (gmt 0)

Yes, it's because of your string syntax again. You need to escape the double quotes embedded within other double quotes:

echo "<td><font color=\"#FFFFFF\">", $row['image_rating'], "</font></td>";

expert_21




msg:1293540
 1:22 pm on Nov 6, 2003 (gmt 0)

ok, so whenever i need to use the quote inside the echo command, i need to use the '\' sign. Thank you! By the way, i am just wondering, how do you guys get so good in php?

davp




msg:1293541
 4:05 pm on Nov 6, 2003 (gmt 0)

PHP allows you to use a single quote or a double quote around strings. As an alternative to using the escape character you could write:

echo '<td><font color="#FFFFFF">', $row['image_rating'], '</font></td>';

You can find more in the PHP reference here [php.net]

jatar_k




msg:1293542
 4:53 pm on Nov 6, 2003 (gmt 0)

how do you guys get so good in php

rofl

4 yrs, 2 companies, many, many contracts and over 3000 posts at WebmasterWorld. Every minute has taught me something and yet I still know nothing.

lasko




msg:1293543
 5:44 pm on Nov 6, 2003 (gmt 0)

how do you guys get so good in php

Started Php and Mysql 8 months ago, have spent hours and hours staring at the screen looking for mistakes just to find a ' in the wrong place.

Today I have two large projects with log in systems, enquiry systems, content management systems and many more.

One lesson I learn't the hard way was to use the Date format in Mysql instead of Varchar etc. now I understand how it works it has made all my projects run perfectally :)

Once you start to learn the basics you can get carried away :)

I would just like to put my thanks to all those who help on this board for Php, its not the sort of thing you can ask any old person how to do it. :)


See also
Commonly Used MySQL Commands [webmasterworld.com] - msg 9
Developing MySQL search query [webmasterworld.com]

jatar_k

[edited by: jatar_k at 8:36 pm (utc) on Nov. 17, 2003]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved