[RESOLVED] Need help with code

**Blue1974** · Apr 9th, 2010, 09:12 AM

I could use some help understanding what my code is doing or trying to do. I have an html form where you select an isbn from a drop down menu. Once you select an isbn and click update the action is to go to update.php. Before this happens, my php code selects the isbn field in the book table and counts the number of rows? Does this sound correct?

The loop portion of my code, which is this:

PHP Code:


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

         $select = mysql_fetch_array("$result_2")

         echo "<option>" . $select[ ISBN ];

        }

Is this what counts the number of rows in the book table? This value is what gets stored in the $num_rows variable? I'm confused because I thought the array variable i is the variable which holds the total number of rows after the loop is completed?

PHP Code:


<html>

<head>

  <title>Update Price</title>

</head>



<body>

  <h1>Update Price</h1>



  <form name="test" action="update.php" method="post">

    <table border="0">

      <tr>

        <td>ISBN

         <select name="ISBN">

         <option> 0-672-31697-8 </option>

         <option> 0-672-31769-9 </option>

         <option> 0-672-31509-2 </option>

         <option> 0-672-31745-1 </option>

         </td>

       <tr>

       <?PHP

       include("connect.php");

       $query_2 = "Select ISBN from books;"; #query

       $result_2 = mysql_query($query_2) or die ("Query 2 Failed!"); #run the query

       $num_rows = mysql_num_rows($result_2); #returns an "int" number of rows

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

         $select = mysql_fetch_array("$result_2")

         echo "<option>" . $select[ ISBN ];

        }

         ?>

         </select>

 <td>Price $</td>

         <td><input type="text" name="price" maxlength="7" size="7"></td>

      </tr>

      <tr>

         <td colspan="2"><input type="submit" name="update" value="Update">

         </td>

      </tr>

    </table>

  </form>

</body>

</html>

**techgnome** · Apr 9th, 2010, 10:04 AM

Code:

$num_rows = mysql_num_rows($result_2);

That line right there gets the total number of rows that was returned by the query.

Code:

for($i=0; $i<=$num_rows; $i++)

This does three things. (first on a side not, the $i is not an "array variable" ... it is simply a variable of type int and is used as a counter).
1) Creates the variable $i and sets its initial value to 0. $i=0
2) Creates the condition under which the loop will continue. $i<=$num_rows - meaning as long as $i is less than or equal to $num_rows, keep looping.
3) Provides the incrementation for the counter. $i++ - Add 1 to $i on each successful loop.

After the loop, $i would hold the total number of rows returned... but since it's only declared with the loop, it doesn't actually exist outside the loop. But $num_rows will still hold the total number of rows returned.

-tg

**Blue1974** · Apr 9th, 2010, 09:02 PM

Sorry, I'm still confused with this operation. Did you mean that we already have the total number of rows from the query before we run the loop? I thought the purpose of the loop was to find the total number of rows in the query?

Question about this statement:

PHP Code:


$num_rows = mysql_num_rows($result_2);

This statement passes the parameter $result_2 to a method and it returns a result which is the total number of rows and then stores the result in the variable $num_rows?

I don't see where the parameter get's passed to?

**techgnome** · Apr 9th, 2010, 09:15 PM

No... the loop doesn't count the rows... it's actualy writing out output to the client browser... a list of <option> tags...That's what the echo line does.

This statement passes the parameter $result_2 to a method and it returns a result which is the total number of rows and then stores the result in the variable $num_rows?

Right... exactly. $result_2 contains the results of the query that was executed.

I don't see where the parameter get's passed to?

You lost me...

-tg

**Blue1974** · Apr 9th, 2010, 10:18 PM

I think what I was trying to ask is where is the method that counts the rows. I've read about a Math class in Java where you pass Math.sqrt(9) and it returns the result. Does this:

$num_rows = mysql_num_rows($result_2);

work in a similar way to how that Math method works in Java when you are using the method that calculates the $num_rows in php?

**kows** · Apr 9th, 2010, 10:28 PM

no, not really? it's just a function (just like any other user-created function, I suppose). mysql_num_rows() is a a part of the MySQL extension for PHP; it returns the row count for a SELECT query. it doesn't do any math to figure out how many rows were returned, though. MySQL counts the number of rows returned from that query and returns the value.

**Blue1974** · Apr 10th, 2010, 10:22 AM

I was just wondering where the function was written or why I couldn't see the method that was returning the number of rows. I thought that mysql_num_rows() was a user written function and that I had to write a method that would count the rows in the data base. You mentioned it was part of the MySQL extension for PHP and when you call mysql_num_rows() with a query as the parameter it will return the number of rows for that query. I think understand it.

My text also shows it in this format:
$num_results = $result->numRows();

The book says that you have control if you make the user select from a list of ISBNs rather then let them type it in.

This is another version of the same form but it's saved as a php extension rather than html.

Code:

<html>
<head>
  <title>Update Price</title>
</head>

<body>
  <h1>Update Price</h1>

  <form name="test" action="update.php" method="post">
    <table border="0">
	  <tr>
	    <td>ISBN
		 <select name="ISBN">
	   <?PHP
	   include("connect.php");
	   $query = "Select isbn from books;"; #query
       $result = mysql_query($query) or die ("Could not run select!"); #run the query
       $num_rows = mysql_num_rows($result); #returns an "int" number of rows
         for($i=0; $i<=$num_rows; $i++){
	     $select = mysql_fetch_array($result);
	     echo "<option>" . $select['isbn'];
	     }
	   ?>
		 </select>
		 </td>
		 </tr>
 <td>Price $</td>
         <td><input type="text" name="price" maxlength="7" size="7"></td>
      <tr>
         <td colspan="2"><input type="submit" name="update" value="Update">
         </td>
      </tr>
    </table>
  </form>
</body>
</html>

I didn't understand it at first but the loop within the PHP is displaying the drowdown menu of the ISBN numbers where as before I was doing it in the HTML? I understand it a little better after your explanations. I appreciate it.

Which method would make more sense to use?

As was said in other posts that you want the html to be html and not be echoed by the php, so the first html method would be the preferred way of setting up this application? Does this sound right?

**Blue1974** · Apr 10th, 2010, 11:13 AM

Ok, the action on my form is action="update.php".

When I click the update button on my form I'm directed to that file and the code for update is:

PHP Code:


if($_POST['update']){



   $query_2 = "update books set price = 'value' where isbn ='$result_2'";



   $result_2 = mysql_query($query_2) or die ("Query 2 Failed!");



   if($query_2){

    echo "Update Successful";

   }

}

I run my form. I select an isbn, enter a value for price and then click update. This seems to work fine. The result in a new window is "Update Successful". Does my code automatically link to the MySQL database and change the price field? When I do a select of the prices from the books table the price hasn't changed for the isbn that was selected.

**kows** · Apr 10th, 2010, 01:36 PM

your script does not "automatically link to the database." there is an include that you have called "connect.php" (as used in your previous scripts) that connects to the database for you. if your update script is not including this file, you wouldn't be able to do anything.

anyway -- the update is successful because it didn't fail, that's all. it doesn't mean that something was actually updated. it just means that the update statement was sent to the database, and the database executed it successfully. it may not have actually affected anything, though! this is where a lot of people seem to get confused. all the database is doing is taking your statement and executing, and telling you whether or not that statement failed or succeeded to execute. you can find out if that statement actually affected any rows by calling mysql_affected_rows(). this function will tell you how many rows were affected by the last statement sent via mysql_query() (which basically means any UPDATE, INSERT, DELETE, or REPLACE statements, because SELECT does not affect anything. SELECT returns the number of results found via mysql_num_rows()). you might use it like so, taking from your previous example:

PHP Code:


if($_POST['update']){

   $query_2 = "update books set price = 'value' where isbn ='$result_2'";

   $result_2 = mysql_query($query_2) or die ("Query 2 Failed!");

   if($query_2){
    echo "Update Successful -- " . mysql_affected_rows() . " ISBNs updated";
   }
}

now, if you ran this code, it would probably say: "Update Successful -- 0 ISBNs updated." This is simply because you're trying to update things that don't exist in your database. let's take a look:

PHP Code:


$query_2 = "update books set price = 'value' where isbn ='$result_2'";

our SQL says we want to update the books table and change the price field to value where ever the ISBN is equal to the value of $result_2. however, $result_2 (as far as I know) is a variable with a call to mysql_query() from your previous script. in this script, I don't see it existing. you should be putting a $_POST variable in here (like $_POST['ISBN'], because that's the name of the <select> on your form). then, we have another $_POST variable for the price (you're using value there, which is a string, where you should be using something like $_POST['price']). but, this is raw user data and you should NEVER send raw user data directly to the database. we need to sanitize it using a function like mysql_real_escape_string() -- this will escape any "bad" characters for us so that we don't need to worry about things like SQL injection (warning: lengthy post), which a malicious user could use to break our website. we can use this function like so (remember, this should be happening before you give $query_2 a value in your script):

PHP Code:


$isbn = mysql_real_escape_string($_POST['ISBN']);
$price = mysql_real_escape_string($_POST['price']);

and now that we've sanitized our user input, we can make our SQL query:

PHP Code:


$query_2 = "UPDATE books SET price='$price' WHERE isbn='$isbn';";

we've now changed our SQL to say that we want to update the books table and change the price field to the value of $price where ever the ISBN is equal to the value of $isbn.

I hope that covers that!

-------

now, onto the question of which of these forms to use. they're practically the same, actually, and neither of them lets the user input an ISBN, so I'm not sure what you're trying to get at there. but, yes, it's definitely easier to use a form when you're asking a user to select an ISBN from a drop down list rather than asking them to enter in one manually. however, this doesn't make it easier to control anything. remember, a form can be manipulated by a user: it's just HTML. if you were using a drop down list on your website, I could duplicate your form (on my own website, for example) and make a text box that was named ISBN and then type in my own ISBN and make it submit to your update script. this is where you get into security and stuff like that, though, and you don't necessarily need to worry about it much now. just know that you can't just assume things are safe.

since I went off on a little tangent: the scripts you posted were almost identical. the first script has a syntax error in the HTML:

Code:

        <td>ISBN
         <select name="ISBN">
         <option> 0-672-31697-8 </option>
         <option> 0-672-31769-9 </option>
         <option> 0-672-31509-2 </option>
         <option> 0-672-31745-1 </option>
         </td>
       <tr>
       <?PHP
       include("connect.php");
       $query_2 = "Select ISBN from books;"; #query
       $result_2 = mysql_query($query_2) or die ("Query 2 Failed!"); #run the query
       $num_rows = mysql_num_rows($result_2); #returns an "int" number of rows
         for($i=0; $i<=$num_rows; $i++){
         $select = mysql_fetch_array("$result_2")
         echo "<option>" . $select[ ISBN ];
        }
         ?>
         </select>

the red </td> and </tr> should not be there. this will break the <select>, because you've got some hard-coded ISBN values (shown in green) followed by some database-driven ISBN values (shown in blue). you're prematurely ending the <select> by adding those extra ending HTML tags, and this may produce unexpected results. the </td> and </tr> should come after the </select>, which is how it is in the second script you've posted. in the second script you also don't have hard-coded ISBN values (the green ones in my example), and they're all retrieved from the database instead.

you also have a syntax error in your HTML that's being spit out by PHP (the blue), and here is the "fix" (changes in red):

Code:

	   <?PHP
	   include("connect.php");
	   $query = "Select isbn from books;"; #query
       $result = mysql_query($query) or die ("Could not run select!"); #run the query
       $num_rows = mysql_num_rows($result); #returns an "int" number of rows
         for($i=0; $i<=$num_rows; $i++){
	     $select = mysql_fetch_array($result);
	     echo "<option>" . $select['isbn'] . "</option>";
	     }
	   ?>

now, as we've said in previous posts, it's never a great idea to be using echo to emit HTML. but, what you're doing here may be considered acceptable. I would do something a bit different to promote readability, which I would argue is also easier to follow for a beginner like you (using a for() statement for returning results from a database is definitely possible, but I would never recommend it -- especially not for you if you're going to think that's the "right" way of doing things):

PHP Code:


<select name="ISBN">
<?php
  include("connect.php");

  //our SQL statement
  $sql = "SELECT * FROM books";

  //send this query to the database and get a result
  $result = mysql_query($sql);

  //now, loop through our result until there are no more results
  while($row = mysql_fetch_assoc($result)){
    //print our stuff
?>
  <option value="<?php echo $row['isbn']; ?>"><?php echo $row['isbn']; ?></option>
<?php
  } //end while
?>
</select>

both the code you posted and the code I've just created accomplish the same thing. I just think that mine is easier to read/understand.

hope that helps -- sorry for the excruciatingly long post!

**Blue1974** · Apr 10th, 2010, 09:18 PM

No problem about the long post. Sorry to make you go through all that though. I've read through it three times now, trying to grasp it all. I wasn't even close to having it set up right.

The original code in my update.php file was:

PHP Code:


$query_2 = "update books set price = "4.99" where isbn = '1-010-23456-1'";

They must have wanted me to update books for that one single isbn but I didn't know how it related to making selections on my form. Maybe they were trying to show me that the variables could equal those values if I choose '1-010-23456-1' and type in a price of 4.99. My query would give me the error message that query faild, then when I changed my line to:

PHP Code:


$query_2 = "update books set price = 'value' where isbn ='$result_2'";

I was at least getting "Query Successful" message.

I forgot it was there, but the include of connect.php was in that file. It was at the top and not in the code I pasted. There were queries for insert, update and select and I was only working with the update query. So my query was actually accessing MySQL but not really doing anything without the $_Post in front of the ['ISBN']. I'm still a little foggy on that but I will just accept that it needs to be like that or rather than just a string.

I knew what the price was for that ISBN before I tried to update it and when I went into MySQL just to see if it did change I found that it hadn't. It makes sense though about using the function you mentioned, mysql_affected_rows() to get confirmation if anything actually changed.

I wasn't even thinking about the aspect that people familiar with html could still bypass the drop down menu and submit a different isbn. I think my text was just trying to tell me that having a drop down list was superior then having someone type a selection in. The drop down list doesn't give the user an opportunity to mistype something. Someone like me with terrible typing skills would easily leave out a dash or be off a digit in the sequence.

Thanks for showing me the while loop. I'm still trying to sort it out but anything to make it easier to read is helpful.

I tried inserting the code you showed me and I successfully updated the price on the isbn I selected. That was great! It was sure easy to take things for granted on the Internet when you don't really know what is taking place behind the scenes like what you just walked me through and that's probably as easy as it gets. Am I right? Wow!!

Again, sorry you had to go through so much. I'll keep going over this and hopefully it will make sense.
Thank you.

**kows** · Apr 11th, 2010, 09:06 AM

you don't need to apologize for anything :)

Thread: [RESOLVED] Need help with code

Thread Tools

Display

[RESOLVED] Need help with code

Re: Need help with code

Re: Need help with code

Re: Need help with code

Re: Need help with code

Re: Need help with code

Re: Need help with code

Re: Need help with code

Re: Need help with code

Re: Need help with code

Re: [RESOLVED] Need help with code

Posting Permissions