[SOLVED] Displaying data from table and being able to change specific values

[davidkk]

I'm still a novice with PHP and I am writing a script that only pulls and displays the information from a table that has a "false" value in the $invis field inside the row. In addition to only displaying rows with that value I wanted to add a button that when clicked changes the value of $invis from it's initial state (false) to true just for that row so when the script is reloaded that row now longer shows up. So forth and so on.

 

Here is what I've written so far, and I can't get it to display more than one row, even if several in the table have a "false" value in $invis

 

<?php

 

 

$con = mysql_connect("*******","*********","******");

 

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

 

mysql_select_db("metropo_artrequest", $con);

 

 

$result = mysql_query("SELECT * FROM artrequests");

 

echo "<table border='1'>

<tr>

<th>Design Number</th>

<th>Color</th>

<th>Quantity</th>

<th>Due Date</th>

<th>Description</th>

<th>First Name</th>

<th>Email</th>

<th>phone</th>

<th>Greek</th>

<th>School</th>

<th>State</th>

<th>Timestamp</th>

<th>Post to Filemaker</th>

</tr>";

 

 

 

while($row = mysql_fetch_array($result))

  {

if ($invis = "false")

{

  echo "<tr>";

  echo "<td>" . $row['designnum'] . "</td>";

  echo "<td>" . $row['color'] . "</td>";

  echo "<td>" . $row['quant'] . "</td>";

  echo "<td>" . $row['duedate'] . "</td>";

  echo "<td>" . $row['desc'] . "</td>";

  echo "<td>" . $row['fname'] . "</td>";

  echo "<td>" . $row['email'] . "</td>";

  echo "<td>" . $row['phone'] . "</td>";

  echo "<td>" . $row['greek'] . "</td>";

  echo "<td>" . $row['school'] . "</td>";

  echo "<td>" . $row['state'] . "</td>";

  echo "<td>" . $row['timestamp'] . "</td>";

  echo "<td><form method=post action=dbeditor.php><input name=invis2 id=invis2 type=text size=15

 

value=$invis><input name=Submit type=submit value=POSTED></form></td>";

 

  echo "</tr>";

  }

exit;

 

  }

echo "</table>";

 

mysql_close($con);

 

 

?>

 

 

 

 

If it's too much of a mess to help sort me through if you can provide me with links that can help me progress I would greatly appreciate it, or if I had missed some similar entries in search because I did not see anything that was like this problem after several attempts of searching.

Thanks

 

[premiso]

Please use [ code] [ /code] tags instead of the quotes (remove the initial space of course)

 

Since your code was no where near complete I made a few changes with comments:

<?php
$con = mysql_connect("*******","*********","******") or die('Could not connect: ' . mysql_error());

/* No need for this if, it can be done as shown above.
if (!$con) {
die('Could not connect: ' . mysql_error());
}
*/ 

mysql_select_db("metropo_artrequest", $con);

$result = mysql_query("SELECT * FROM artrequests WHERE invis = " . false ); // changed this so you do not need the if in the loop

// added form code here, there is no need for multiple forms.
echo "<form method=post action=dbeditor.php>
<table border='1'>
<tr>
<th>Design Number</th>
<th>Color</th>
<th>Quantity</th>
<th>Due Date</th>
<th>Description</th>
<th>First Name</th>
<th>Email</th>
<th>phone</th>
<th>Greek</th>
<th>School</th>
<th>State</th>
<th>Timestamp</th>
<th>Post to Filemaker</th>
</tr>";

while($row = mysql_fetch_assoc($result)) { // changed to assoc cause that is what you are pulling out
// where is $invis being set at? also a single = generally returns true cause it is an assignment operator... if ($invis = "false") { 
// if is no longer needed, see the query above. if ($row['invis'] == false) {
  echo "<tr>";
  echo "<td>" . $row['designnum'] . "</td>";
  echo "<td>" . $row['color'] . "</td>";
  echo "<td>" . $row['quant'] . "</td>";
  echo "<td>" . $row['duedate'] . "</td>";
  echo "<td>" . $row['desc'] . "</td>";
  echo "<td>" . $row['fname'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "<td>" . $row['phone'] . "</td>";
  echo "<td>" . $row['greek'] . "</td>";
  echo "<td>" . $row['school'] . "</td>";
  echo "<td>" . $row['state'] . "</td>";
  echo "<td>" . $row['timestamp'] . "</td>";
  // this will be named the same each time the loop goes through... echo "<td><input name=invis2 id=invis2 type=text size=15 value=$invis></td>"; 
  // Let's make it a checkbox array instead
  echo "<td><input name=\"idval[]\" id=\"invis2\" type=\"checkbox\" value={$row['id']}></td>"; // this assumes that you do have a unique identifier column called "id"
  echo "</tr>";
  // } if is no longer needed since the query is modified to only pull out the data where invis = false
// exit; Why is this exit here? this kills the script after 1 loop and is not needed.
}
echo "</table>
<input name=Submit type=submit value=POSTED></form>"; // the ending to the form so it can be submitted.

// this is not needed, has been depreciated. mysql_close($con);
?>

 

Read through those comments to see why I changed what I did.

 

Example dbeditor.php

<?php
$con = mysql_connect("*******","*********","******") or die('Could not connect: ' . mysql_error());
mysql_select_db("metropo_artrequest", $con);

// test if the form was submitted
if (isset($_POST['Submit'])) {
       // it was, see if any checkboxes were checked
if (isset($_POST['idval']) && is_array($_POST['idval'])) {
               // there was, let's implode the array to format it for SQL insertion using the "IN" keyword.
	$ids = implode(", ", $ids);
	mysql_query("UPDATE artrequests SET `invis` = true WHERE id IN({$ids})") or die(mysql_error());
	echo mysql_rows_affected() . ' rows have been made invisible!';
}
}
?>

 

Since you did not post the dbeditor, I made an example one of how you would update those values once submit was clicked.

 

The code I created does some assuming, such as artrequests has a primary or unique key of "id" and that invis is the column name that you are checking.

 

EDIT:

Since you are actively trying to learn, reply here if any of the above is confusing or you would like clarification. I would be happy to provide it.

 

EDIT EDIT:

Fixed the sql query, I over looked that part.

[.josh]

if you are only going to display/work with the ones that are false, then just select the ones that are false in your query...

 

select * from artrequests where invis = 'false'

[davidkk]

I appreciate your quick help. Your comments help explain a lot and I can follow along the syntax reasonably well.

 

The code I created does some assuming, such as artrequests has a primary or unique key of "id" and that invis is the column name that you are checking.

 

invis is a field in each row that it being checked for 'true' or 'false' however for the unique key of "id" I don't have that in the table. If I were to add that column in artrequests with a value of id would I need a script to give id values or does that happen when the the data is fetched?

 

// where is $invis being set at?

$invis is set when the data is inserted into the database from another script, all the rows need to visible at first until the person running the script checks the row as invisible

[premiso]

however for the unique key of "id" I don't have that in the table. If I were to add that column in artrequests with a value of id would I need a script to give id values or does that happen when the the data is fetched?

 

You want to alter the table, add "id" as a primary key INT auto_increment value and it should fill it in. This will allow for you to modify certain rows, without that you really cannot tell which row to modify. This will automatically fill itself in when inserting any data.

 

In the original code you just had $invis, which was not being set anywhere.

 

Anyhow a few alterations to my code above:

<?php
$con = mysql_connect("*******","*********","******") or die('Could not connect: ' . mysql_error());
mysql_select_db("metropo_artrequest", $con);

// test if the form was submitted
if (isset($_POST['Submit'])) {
       // it was, see if any checkboxes were checked
   if (isset($_POST['idval']) && is_array($_POST['idval'])) {
               // there was, let's implode the array to format it for SQL insertion using the "IN" keyword.
      $ids = implode(", ", $ids);
      mysql_query("UPDATE artrequests SET `invis` = 'true' WHERE id IN({$ids})") or die(mysql_error());
      echo mysql_rows_affected() . ' rows have been made invisible!';
   }
}
?>

 

And:

<?php
$con = mysql_connect("*******","*********","******") or die('Could not connect: ' . mysql_error());
mysql_select_db("metropo_artrequest", $con);

$result = mysql_query("SELECT * FROM artrequests WHERE invis = 'false'"); // modified this to be the literal false, not sure if this is correct or not.

echo "<form method=post action=dbeditor.php>
<table border='1'>
<tr>
<th>Design Number</th>
<th>Color</th>
<th>Quantity</th>
<th>Due Date</th>
<th>Description</th>
<th>First Name</th>
<th>Email</th>
<th>phone</th>
<th>Greek</th>
<th>School</th>
<th>State</th>
<th>Timestamp</th>
<th>Post to Filemaker</th>
</tr>";

while($row = mysql_fetch_assoc($result)) { 
     echo "<tr>";
     echo "<td>" . $row['designnum'] . "</td>";
     echo "<td>" . $row['color'] . "</td>";
     echo "<td>" . $row['quant'] . "</td>";
     echo "<td>" . $row['duedate'] . "</td>";
     echo "<td>" . $row['desc'] . "</td>";
     echo "<td>" . $row['fname'] . "</td>";
     echo "<td>" . $row['email'] . "</td>";
     echo "<td>" . $row['phone'] . "</td>";
     echo "<td>" . $row['greek'] . "</td>";
     echo "<td>" . $row['school'] . "</td>";
     echo "<td>" . $row['state'] . "</td>";
     echo "<td>" . $row['timestamp'] . "</td>";

     echo "<td><input name=\"idval[]\" id=\"invis2\" type=\"checkbox\" value={$row['id']}></td>"; // this assumes that you do have a unique identifier column called "id"
     echo "</tr>";
}

echo "</table>
<input name=Submit type=submit value=POSTED></form>"; // the ending to the form so it can be submitted.
?>

 

Hopefully that helps you out a bit more, as CV said (And I iterated above) only select the data you want in the query, this saves coding time and processing time.

[davidkk]

Again I appreciate the quick replies. Right now I'm trying to create the the id field with a primary key, however I am getting the

#1062 - Duplicate entry '0' for key 1  error. I tried creating an entirely new database with the same fields as artrequests and getting the primary key for id set when it was created. Even though the new name of the table is reflected in the script that inserts the data, it is not getting any of the values inserted into it at all so I am working on figuring that out.

Thanks again

[premiso]

Show the portion where you insert data, and I would be glad to help you fix that issue.

[davidkk]

I got it to work, it wasn't inserting data into the tables because I didnt add an insert for id. So right now when I run the scrip it shows all the rows with invis being a value of false and check boxes next to them. When I check one and click on POST/ED it returns an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

 

the header is dbeditor.php

 

However if a box is not checked and I POST it runs fine (obviously returns a blank screen since nothing happened as a result of no box checked)

[premiso]

Let's try this out and tell us the error:

 

<?php
$con = mysql_connect("*******","*********","******") or die('Could not connect: ' . mysql_error());
mysql_select_db("metropo_artrequest", $con);

// test if the form was submitted
if (isset($_POST['Submit'])) {
       // it was, see if any checkboxes were checked
   if (isset($_POST['idval']) && is_array($_POST['idval'])) {
               // there was, let's implode the array to format it for SQL insertion using the "IN" keyword.
      $ids = implode(", ", $ids);
      $sql = "UPDATE artrequests SET `invis` = 'true' WHERE id IN({$ids})";
      mysql_query($sql) or die("ERROR IN SQL: {$sq} \n Mysql Returned: " . mysql_error());
      echo mysql_rows_affected() . ' rows have been made invisible!';
   }
}
?>

 

Replace that then run it again and report back the error. I have a feeling it has to do with an element of the id array being empty. While you are doing that, view the source on the created page and make sure the value of the checkbox contains something, if not we may have to revisit that portion too.

[davidkk]

ERROR IN SQL: 

Mysql Returned: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

 

Error now,

 

Source from page generated:

 

<form method=post action=dbeditor.php>
<table border='1'>
<tr>
<th>Design Number</th>
<th>Color</th>
<th>Quantity</th>
<th>Due Date</th>
<th>Description</th>
<th>First Name</th>
<th>Email</th>

<th>phone</th>
<th>Greek</th>
<th>School</th>
<th>State</th>
<th>Timestamp</th>
<th>Post to Filemaker</th>
</tr><tr><td>test</td><td>test</td><td>test</td><td>test</td><td>Please enter any changes to online design.</td><td>test</td><td>davidkkimball@yahoo.com</td><td>test2t</td><td>test</td><td>test</td><td>Maine</td><td>02/09/09</td><td><input name="idval[]" id="invis2" type="checkbox" value=1></td></tr><tr><td>test</td><td>test</td><td>test</td><td>test</td><td>Please enter any changes to online design.</td><td>test</td><td>davidkkimball@yahoo.com</td><td>test2t</td><td>test</td><td>test</td><td>Maine</td><td>02/09/09</td><td><input name="idval[]" id="invis2" type="checkbox" value=2></td></tr></table>

<input name=Submit type=submit value=POSTED></form>

 

Is there anything I can check from phpmyadmin that will be helpful as far as getting a better idea how I have the table set up and if in turn if it may cause additional problems?

 

[premiso]

Typo on my part:

 

mysql_query($sql) or die("ERROR IN SQL: {$sql} \n Mysql Returned: " . mysql_error());

 

Replace that in the dbenter file, I did $sq, instead of $sql.

 

The form looks good.

[davidkk]

ERROR IN SQL: UPDATE artrequests SET `invis` = true WHERE id IN() Mysql Returned: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

 

New error?

 

Forgot to mention were right in assuming that $invis is just a literal false.

[premiso]

Well I am a complete moron:

 

<?php
$con = mysql_connect("*******","*********","******") or die('Could not connect: ' . mysql_error());
mysql_select_db("metropo_artrequest", $con);

// test if the form was submitted
if (isset($_POST['Submit'])) {
       // it was, see if any checkboxes were checked
   if (isset($_POST['idval']) && is_array($_POST['idval'])) {
               // there was, let's implode the array to format it for SQL insertion using the "IN" keyword.
      $ids = implode(", ", $_POST['idval']); // fixed here.
      $sql = "UPDATE artrequests SET `invis` = 'true' WHERE id IN({$ids})";
      mysql_query($sql) or die("ERROR IN SQL: {$sql}\n Mysql Returned: " . mysql_error());
      echo mysql_rows_affected() . ' rows have been made invisible!';
   }
}
?>

 

Try that and see if it works. Basically I was not calling the post array like I should have been, which was the issue.

[davidkk]

Yes it works perfectly now thank you very much! If you have any suggestions on good php resources at my level I'd appreciate it.

Again thanks a ton for the help. I'll be reading up.

[premiso]

Your best one is reading the PHP Manual anytime you say, "I wonder if I can do this?" or "Is this possible". Search the manual, chances are there is already a function for it and examples of usage.

 

Like just there you learned implode with usage of $_POST and how to properly display MySQL data. If you notice at the mysql_query and the mysql_fetch_assoc man pages, you can see that there are examples of proper usage for that, which might have helped you.

 

If all else fails try googling for a tutorial of what you would like done.  As a final resort, after trying to code it, ask here for help. We will surely point you in the right direction as long as you have at least put effort into trying to make it work.

 

EDIT:

I would however read up on MySQL and 3NF (3rd normalized form), as it will teach you proper database structure and usage.