PHP - Editing Records With Php/mysql

Hello

Im quite confused at what filtering I should use on my data when pulling it from a MySQL database.  I don't sanitize my data on input because I am using prepared statements with PHP's PDO Driver which means I don't need to use mysql_real_escape_string() at all.

When I pull the data to be displayed i.e. in a HTML Table I use the below function to make it safe for HTML output.


public static function htmlSafe($data)
{ return nl2br(htmlentities($data, ENT_QUOTES)); }


However the rules change when Im using a HTML Form to edit the data, and I am unsure what I need to strip out. I.e. What would I need to do to make all data safe to insert into the following form input.


<input id = "someInput" type = "text" value = "<?php echo $someVarThatNeedsFiltering ?>" />


Also, one more question, in my html attributes (Valid ones like class, name, id, style, _target) I use a mixture of double quotes(") and single quotes ('), for quoting my values. Which one should I use or which one is more valid, doubles, or singles?

I have a problem .
I 've been trying for a long time to make an update for php mysql to change the data.
but every time I do not manage to make it work with a form.
but it works if I only if I put this ($ sql = "UPDATE users SET username = 'value' WHERE id = 10 " ; )
so it only works when I put the value of the id.

but I want in an html form to indicate what I want to change and what id goes.

but I have tried so long that I do not feel like I so want someone help me.

make the same database and same as my records and make the code and test it if it works show me please
my database name : web test
my table called : users
my records are called :

id int ( 11) AUTO_INNCREMENT
username , varchar ( 255 )
password , varchar ( 255 )
first_name , varchar ( 255 )
last_name , varchar ( 255 )
email, varchar ( 255 )
Age, int ( 11)             Look, my update.php is like this now
<?php
$servername = "localhost";
$username = "root";
$password = ".....";
$dbname = "webtest";


$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$sql = "UPDATE users SET password='cotton candy' WHERE id=10";

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
?>
but now i have still have to go into the php file to change the valeu or the id 
but i looked on site and youtube how to put it in a simple html form 
but it still does not work.

i want it in a html from.
I want that when I enter the ID that the data of the user appears and that I can change any valeu separately.

Hello,

This is driving me crazy, as I cannot get it working.

I have a database that has a table called 'categories'. I need to retrieve all categories with category_id != '1,2,3'. For this, I can this query:

Code: [Select]
$q = "SELECT * FROM categories WHERE category_id NOT IN (1,2,3)";

The query executes fine. I need this categories retrieved, to be passed to a Smarty Template Engine powered page. I have:
Code: [Select]
$q = "SELECT * FROM categories WHERE category_id NOT IN (1,2,3)";
$res = mysql_query($q);
while($row=mysql_fetch_array($res)) {
 Assign('categories',$row);
}
But when retrieving the array in the Smarty page, with a loop, I only see the last item of the array (the last category).

If I go back to the while loop, and add a Code: [Select]
print_r($row);, I see two arrays (I have two categories).

But the question is: How can I work with those two categories, as I need to use them individually outside the while loop?

Any help is appreciated.

Best Regards,
Richi

Hi guys,

I need your help. I am checking on a database as I want to see if the records have been updated or not.

Code: [Select]
<?php
session_start();
    define('DB_HOST', 'localhost');
    define('DB_USER', 'mydbuser');
    define('DB_PASSWORD', 'mydbpass');
    define('DB_DATABASE', 'mydbtable');
       
    $errmsg_arr = array();
    $errflag = false;

    $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    if(!$link) {
  die('Failed to connect to server: ' . mysql_error());
    }

    $db = mysql_select_db(DB_DATABASE);
    if(!$db) {
  die("Unable to select database");
    }

   function clean($var){

return mysql_real_escape_string(strip_tags($var));
    }
  
    $username = clean($_GET['user']);
    $password = clean($_GET['pass']);
    $test = clean($_GET['test']);
    $public = clean($_GET['public']);
   
   
   if (isset($_GET['user']) && (isset($_GET['pass']))) {
    if($username == '' || $password == '') {
  $errmsg_arr[] = 'username or password are missing';
  $errflag = true;
    }
} elseif (isset($_GET['user']) || (isset($_GET['test'])) || (isset($_GET['public']))) {
    if($username == '' || $test == '' || $public == '') {
  $errmsg_arr[] = 'user or others are missing';
  $errflag = true;
    }
  }

    if($errflag) {
  $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
  echo implode('<br />',$errmsg_arr);
   }
   else {
  $qry="SELECT * FROM members WHERE username='$username' AND passwd='$password'";
  $result=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error());


if ($username && $password) {
  if(mysql_num_rows($result) > 0) {
     $qrytable1="SELECT images, id, test, links, Public FROM user_channel_list WHERE username='$username'";
    $result1=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());

    while ($row = mysql_fetch_array($result1)) {
        echo "<p id='test'>";
        echo $row['test'] . "</p>";
        echo '<p id="images"> <a href="images.php?test=test&id='.$row['id'].'">Images</a></td> | <a href="http://' .  $row["links"] . '">Link</a> </td> | <a href="delete.php?test=test&id='.$row['id'].'">Delete</a> </td> | <span id="test">'.$row['Public'].'</td>';
     }
   } else {
    echo "user not found";
  }
} elseif($username && $test && $public) {
    $qry="SELECT * FROM members WHERE username='$username'";
    $result1=mysql_query($qry) or die('Error:<br />' . $qry . '<br />' . mysql_error());
    
    if(mysql_num_rows($result1) > 0) {     
      $qrytable1="SELECT Public FROM user_channel_list WHERE username='$username' && test='$test'";
      $result2=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());
      
      if(mysql_num_rows($result2) > 0) {
        $row = mysql_fetch_row($result2);
        mysql_query("UPDATE user_list SET Public=('$_GET[public]') WHERE username='$username' AND test='$test'");
        echo "update!";
      } else {
        echo "already updated!";
      }
    } else {
      echo "user not found";
    }
  }
} 
?>

When I run debug the code on my php, if i input the data in a url bar while the records are the same as the data that I enter in the url, i should get the print out on my php "already updated", but I keep getting "update!".

Do you know how i can check on mysql database to see if the records have been updated or not??

Hi,I'm hoping someone can help me:).  When I add foreign keys to the database in MySQL I CAN change the primary key field and the corresponding indexed field will automatically change giving me the same value which is exactly what i want. The issue is when I try to insert the values using my php code i get the 'cannot update child error'. I have not selected a value for the unique_id field as I want it to inherit the value from the primary key. Here's my code as simplified as I could do it.

<?php  //this calls the class productupload and instantiates the function insert_form to insert values into the db.

$product = new productUpload();
$product->insert_form();?>
//This calls the insert_form function and inserts info into tables
<?php
class productUpload extends DatabaseObject{
protected static $table_name="itm_details";
protected static $db_fields =array('id','unique_id','itm_cat','itm_make','itm_model','itm_desc','itm_cond','itm_date_from','itm_date_to','itm_add_date');     


    public $id;
    public $unique_id;
    public $itm_cat;
    public $itm_make;
    public $itm_model;
    public $itm_desc;
    public $itm_cond;
    public $itm_date_from;
    public $itm_date_to;   
    public $itm_add_date;
Heres the function insert_form which grabs the form submitted data. Note I have not defined a value for $unique_id

public function insert_form(){ 
global $database; 
 //set the object attributes to the form the parameters
        if(isset($_POST['submit'])) {
        $result = array(
        $this->itm_cat =(!empty($_POST['itm_cat'])) ? trim($_POST['itm_cat']) : NULL,
        $this->itm_make =(!empty($_POST['itm_make'])) ? trim($_POST['itm_make']) : NULL,       
        $this->itm_model = (!empty($_POST['itm_model'])) ? trim($_POST['itm_model']) : NULL,                                   
        $this->itm_desc =(!empty($_POST['itm_desc'])) ? trim($_POST['itm_desc']) : NULL,
        $this->itm_cond =(!empty($_POST['itm_cond'])) ? trim($_POST['itm_cond']) : NULL,
        $this->itm_date_from =(!empty($_POST['itm_date_from'])) ? trim($_POST['itm_date_from']) : NULL,
        $this->itm_date_to =(!empty($_POST['itm_date_to'])) ? trim($_POST['itm_date_to']) : NULL,
        $this->itm_add_date = date("Y-m-d H:m:s"));
//check date is numeric
        //if(is_numeric($_POST['itm_date_from'])) {
        //$this->itm_date_from = $_POST['itm_date_from'];
//}   
//check date is numeric                       
        //if(is_numeric($_POST['itm_date_to'])) {
        //$this->itm_date_to = $_POST['itm_date_to'];
        //}
        if($result){
        $result = $this->create();
        }
        }
        }

//here's the create function referred to above which sanitises the posted data and inserts it into the db.


public function create() {
        global $database;
        $attributes = $this->sanitised_attributes();

        $sql = "INSERT INTO ".self::$table_name."(";
        $sql .= join(", ", array_keys($attributes));
        $sql .= ") VALUES ('";
        $sql .= join("', '", array_values($attributes));
        $sql .= "')";
        if($database->query($sql)) {
        $this->id = $database->insert_id();
        return true;
        } else {       
        return false;
        }

}

I'm sorry to be back so soon, but I'm up against another mystery.  I'm using the code below to enter a bunch of css data from a spreadsheet into a mysql table.  I think the data file is OK.  The array created by the script checks out with print_r.  (There are many more records than shown.  I truncated it to save space.)

The problem is that I get this error regarding my sql statement, not the data or anything else:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'check, name, phone, email, entry_fee, print_fee, image_name, description, med...' at line 1 in /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php:242 Stack trace: #0 /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php(242): PDO->prepare('INSERT INTO tbl...') #1 {main} thrown in /Users/studio/Sites/BannerProject/b-as/_test_site/csv_to_array.php on line 242

I've typed it in a dozen times to make sure there are no errors and keep getting the same error.

I tried running a test file and gradually increasing the number of placeholders and at some point I always end up getting the same error,  I can delete the most recent addition and it works again.  Then I can add another placeholder exactly as before and it works the second time.  It feels like a ghost in the machine.

Any idea what I am doing wrong?  An I typing something I don't see?

<?php
	
require '__classes/Db.php';

$csvData = '1,FALSE,Carol Lettko,,,TRUE,FALSE,Carol_Lettko-DSC_3022.jpg,Baby Herons/Brickyard,photo,,,
,,,925-285-0320,cjl164@aol.com,,,Carol_Lettko-DSC_0164.JPG,Heron/Brickyard,photo,,,
,,,,,,,Carol_Lettko-IMG_5723.jpg,Kayaker/Brickyard,photo,,,
,,,,,,,,,,,,
2,FALSE,Louise Williams,,,TRUE,FALSE,Louise_Williams-BirdsOfAFeatherAOPR.jpg,Alligator with Words,Book Excerpt,,,
,,,510-232-9547,lkw@louisekwilliams.com,,,Louise_Williams-Hope-TheFairyChickenAOPR.jpg,Hope The Fairy Chicken,,,,
,,,The d exatrfrfvct/.*tygrvurr,,,,,,,,,
,,,,,,,,,,,,
3,TRUE,Dorothy Leeland,,lelanddorothy@gmail.com,TRUE,FALSE,DJ_Lee-bridge at dusk 700px width.jpg,Bridge,photo,,,
,,,,,,,DJ_Lee-friends 700px width.jpg,Friends,photo,,,
,,,,,,,DJ_Lee-hybiscus 700 px wide.jpg,Hibiscus,photo,,,
,,,,,,,,,,,,
4,FALSE,Rita Gardner,,,TRUE,FALSE,Rita_Gardner-Explosion - Gardner photo.JPG,Explosion,photo,,,
,,,,tropicrita@msn.com,,,Rita_Gardner-Ferry Point tables and chair - Gardner.JPG,Ferry Point Tables,photo,, ,
,,,,,,,Rita_Gardner-Forks - Gardner photo.JPG,Forks,photo,,,
,,,,,,,,,,,,
';

$lines = explode(PHP_EOL, $csvData);
$array1 = array();
foreach ($lines as $line) {
    $array1[] = str_getcsv($line);
}

$stmt = $pdo->prepare("INSERT INTO tbl_person_data (number, check, name, phone, email, entry_fee, print_fee, image_name, description, medium, select, orient, site) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");

    foreach ($array1 as $row)
    {
        $stmt->execute('$row');
    }

echo '<pre>';
print_r($array1);
echo '</pre>';


?>

 

How do you do this? Thanks in advance.

I wonder whether someone can help me please.

I've found http://www.plus2net.com/php_tutorial/ajax-listbox.php tutorial to create a drop down menu using mySQL table data, which, in turn returns a list of results on the page.

Following this tutorial I've put together the tables in my database and the required scripts as shown in the tutorial with the one exception, the "z_db.php" file, which I've assumed to be:

Code: [Select]
<?php mysql_connect("host", "user", "password")or die(mysql_error()); mysql_select_db("database"); ?>

The problem I have, is that when I try and run this, I receive the following error: Quote

Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in /homepages/2/d333603417/htdocs/development/catsearch.php on line 91 which is this line in the search form: echo "</head><body onload="ajaxFunction()";>";.


I must admit I've guessed as to the structure of the 'z_db.php' file should look like because this is not shown so perhaps this is the problem.

I just wondered wether someone could perhaps take a look at this please and let me know where I've gone wrong.


Many thanks and kind regards

Code: [Select]
<?php
require "db/config.php";
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$country = $_POST['country'];
$state = $_POST['state'];
$city = $_POST['city'];
$zcode = $_POST['zcode'];
$address = $_POST['address'];
$ppemail = $_POST['ppemail'];
$pnumber = $_POST['pnumber'];
$cemail = $_POST['cemail'];
$url = $_POST['url'];
$price = "$5.00";
$query = "INSERT INTO custpackage1000(
id,
FirstName,
LastName,
Country,
State,
City,
ZipCode,
Address,
PayPalEmail,
PhoneNumber,
PrimaryEmail,
WebsiteURL) VALUES (
'1',
'$fname',
'$lname',
'$country',
'$state',
'$city',
'$zcode',
'$ppemail',
'$pnumber',
'$cemail',
'$url')";

mysql_connect($host, $user, $pass) or die("<br /><br /><h1>Fatal error. Please contact support if this persists.</h1>");
mysql_select_db($dbname);
mysql_query($query) or die ("could not open db".mysql_error());
sleep(2);
?>
Why won't the code insert into my database upon submission of data? What am I doing wrong?

Hi, I'm new to the forum so this could go in the MySQL section but I'm not sure.

I am trying to make a page that will list all records from a column in HTML table and have a delete button to remove a specific record. I have got to the part where I have listed the records in a table.
Note: Only records from a specific column ('links') are printed.

Here is the code:
Code: [Select]
$con = mysql_connect("localhost","***","***");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("***", $con);

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

echo "<table border='1'>
<tr>
<th>Current links</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['links'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
How would I go about adding a delete button next to each record to delete that specific record?

Thanks for any help.

Hey Guys,

Here is my issue. I have a MySQL database with a table of products. I use this table to generate a mutliselect form element that is used int a larger form. The larger form contains other information relevant to an issue that we are having with that product (such as an outage). Anyway, the form works fine and the form validation works fine and I write it to the database fine. The issue I'm having is how to save the mutliselect options most efficiently.

This is my current process:

1. User creates a new entry from the form and clicks save
2. The form is validated
3. All entries are written to the database in a table called "incidents" (content inclues, timestamps, description of the problem, the impact, who to contact, etc)
4. Because the products list can vary from 1 product to all (~30) products I broke it out into a separate table called "impacted_products". I then loop through each of the selected products and write one row for each product using the id from the row in the "incidents" table to define the tables relationship.

Example of DB results:

"incidents"

+-----------------+------+----------+---------------------+---------+---------+
| notification_id | what | impact   | time_start          | summary | contact |
+-----------------+------+----------+---------------------+---------+---------+
| 235235          | Test | None.... | 2011-02-08 13:41:00 | Test    |       3 |
+-----------------+------+----------+---------------------+---------+---------+

"impacted_products"

+-----+-----------------+------------+---------------------+---------+
| id  | notification_id | product_id | timestamp           | deleted |
+-----+-----------------+------------+---------------------+---------+
| 202 | 235235          |          7 | 2011-02-10 14:30:42 |       0 |
| 203 | 235235          |         37 | 2011-02-10 14:30:42 |       0 |
| 204 | 235235          |         23 | 2011-02-10 14:30:42 |       0 |
+-----+-----------------+------------+---------------------+---------+

5. Now the users wants to go back and make some updates.
6. They click the edit button from the menu on screen and the form is brought back up and all options/fields are filled out from that which is stored in the database.
7. They make their edits and save again.
8. Now this time it simple updates the "incidents" table but for the products table I do a:

"UPDATE impacted_products SET deleted=1 WHERE notification_id='$ID'"

and "delete" all the old impacted_products for this particular incident and then loop through all the products that the user still had selected and write them to the database again.
9. And repeat

So as you can see this could end up with a lot of "useless" entries in the database since my scripts only pay attention to those "impacted_products" whose deleted value is set to 0.

Example:

+-----+-----------------+------------+---------------------+---------+
| id  | notification_id | product_id | timestamp           | deleted |
+-----+-----------------+------------+---------------------+---------+
| 176 | 235235          |         37 | 2011-02-08 15:26:25 |       1 |
| 177 | 235235          |         43 | 2011-02-08 15:26:25 |       1 |
| 178 | 235235          |         37 | 2011-02-08 15:37:58 |       1 |
| 179 | 235235          |         43 | 2011-02-08 15:37:58 |       1 |
| 180 | 235235          |          1 | 2011-02-08 15:39:49 |       1 |
| 181 | 235235          |          7 | 2011-02-08 15:39:49 |       1 |
| 182 | 235235          |         37 | 2011-02-08 15:39:49 |       1 |
| 183 | 235235          |         43 | 2011-02-08 15:39:49 |       1 |
| 184 | 235235          |          1 | 2011-02-08 15:40:53 |       1 |
| 185 | 235235          |          7 | 2011-02-08 15:40:53 |       1 |
| 186 | 235235          |         37 | 2011-02-08 15:40:53 |       1 |
| 187 | 235235          |         43 | 2011-02-08 15:40:53 |       1 |
| 188 | 235235          |         37 | 2011-02-10 10:00:47 |       1 |
| 189 | 235235          |          1 | 2011-02-10 12:17:05 |       1 |
| 190 | 235235          |          7 | 2011-02-10 12:17:05 |       1 |
| 191 | 235235          |         13 | 2011-02-10 12:17:05 |       1 |
| 192 | 235235          |         37 | 2011-02-10 12:17:05 |       1 |
| 193 | 235235          |         23 | 2011-02-10 12:17:05 |       1 |
| 194 | 235235          |          1 | 2011-02-10 12:21:52 |       1 |
| 195 | 235235          |          7 | 2011-02-10 12:21:52 |       1 |
| 196 | 235235          |         13 | 2011-02-10 12:21:52 |       1 |
| 197 | 235235          |         37 | 2011-02-10 12:21:52 |       1 |
| 198 | 235235          |         23 | 2011-02-10 12:21:52 |       1 |
| 199 | 235235          |          7 | 2011-02-10 12:22:26 |       1 |
| 200 | 235235          |         37 | 2011-02-10 12:22:26 |       1 |
| 201 | 235235          |         23 | 2011-02-10 12:22:26 |       1 |
| 202 | 235235          |          7 | 2011-02-10 14:30:42 |       0 |
| 203 | 235235          |         37 | 2011-02-10 14:30:42 |       0 |
| 204 | 235235          |         23 | 2011-02-10 14:30:42 |       0 |
+-----+-----------------+------------+---------------------+---------+

I did it this way beacuase it seems the easiest and fastest way. Otherwise I would have to lookup all the impacted_products from the table that match that notification_id and check

1) Was there a new product selected? If so, add it.
2) Was a product that was selected no longer selected? If so, delete it.
3) Was a product that was selected before still selected now? If so, leave it alone.

This seemed like a lot of extra looping and a lot of extra DB queries to essentially end up at the same place.

However, I feel that there has still got to be a more efficient way of doing this where I won't have all the extra entries in the impacted_products table. 

Any ideas?

Thanks in advance!

I want to pull some records  plus unique key from one mysql table and then use them as questions in a form/questionnaire.

The post form will have six radio buttons  to the right of the fields pulled with the key posted but hidden and will wirte to another table.

I guess this is a templating question- as I've successfully retrieved the table 1 records via a query - I just can't seem to use them in the form. Below is the code I'm using to view table "PCM1"

<?php // query.php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());

$query = "SELECT * FROM pcm1";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " . mysql_error());

$rows = mysql_num_rows($result);

for ($j = 0 ; $j < $rows ; ++$j)
{
echo 'RID: '   . mysql_result($result,$j,'mcde')  ;
echo 'Statement: '    . mysql_result($result,$j,'oppclub')    . '<br />';

;
}

Hi,

I need to compare records in a table based on the 'datetime' field, like if the difference between time is less than 15min.

need to combine the records(rows) as a single resultant row until the time difference between them  is less than 15min and when a record's time difference is >15min that should return as another row and so on. Please find following sample table and the required output format

Sample Table:

Code: [Select]
ID NAME DATETIME
1   aaa    2011-10-10 06:30:00
2   bbb    2011-10-10 06:33:00
3   ccc    2011-10-10 06:38:00
4   ddd    2011-10-10 06:40:00
5   eee    2011-10-10 07:10:00
6   ffff    2011-10-10 07:14:00
7   sss    2011-10-10 08:16:00
8   jjj    2011-10-10 08:26:00
9   kkk    2011-10-10 08:28:00
10  mm    2011-10-10 09:46:00
11  ppp    2011-10-10 09:49:00
12  qqq    2011-10-10 09:52:00


Output Needed :

Code: [Select]
IDs START DATETIME END DATETIME
1,2,3,4 2011-10-10 06:30:00 2011-10-10 06:40:00
5,6 2011-10-10 07:10:00                      2011-10-10 07:14:00
7,8,9 2011-10-10 08:16:00 2011-10-10 08:28:00
10,11,12 2011-10-10 09:46:00                      2011-10-10 09:52:00

You can see 1st row in the output table having IDs 1,2,3,4 coz the time difference between them is less than 15minutes (it doesn't means time difference between start and end; it is actually the  time difference between current record and previous one ; ie; ID-1 and ID-2 have difference less than 15min and ID-2 & ID-3 have difference less than 15min and ID-3 & ID-4 have difference less than 15min, so those 4 records combine together as a single row also shows their start time and end time. Then you can see diff between ID-4 and ID-5 is greater than 15 min so it should display as new row, and so on)

How can I acheive above Output with mysql query ??

Please help..