Looking for some help

[studgate]

I am trying to update my database using php
I have the update.php that is supposed to check
for changes and insert the changes into the database
and into the sorting categories but everytime
there is change, it just add a new entry instead of
updating the previous one, like if the city is Paris
and I change it to Marseille, there is a new entry
for Marseille in the city sorting and the entry for
Paris is empty.  ???
Here is the code, any help is welcome.
Thank you guys!  :)


<?
$dbconn = @mysql_connect("localhost","user","passwordhere");
@mysql_select_db("databasehere");


$strsql = "update tblreports set `event_date`='$event_date', `event_time`='$event_time', `kb`='$kb', `event_duration`='$event_duration', `city`='$city', `state`='$state', `zip`='$zip', `foreign_country`='$foreign_country', `county`='$county', `nr_witness`='$nr_witness', `activity`='$activity', `location_type`='$location_type', `location_age`='$location_age', `description`='$description', `summary`='$summary', `characteristics`='$characteristics', `uname`='$uname', `uemail`='$uemail', `uaddress`='$uaddress', `ucity`='$ucity', `ustate`='$ustate', `uzip`='$uzip', `uphone`='$uphone', `uoccupation`='$uoccupation', `religy`='$religy', `education`='$education', `uage`='$uage', `ugender`='$ugender', `medication`='$medication', `medication_recent`='$medication_recent', `illness`='$illness', `drug`='$drug', `drug_recent`='$drug_recent', `alcohol`='$alcohol', `alcohol_recent`='$alcohol_recent', `anyuseof`='$anyuseof', `charlast`='$charlast' where id='$editid'";


@mysql_query($strsql);

list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbyedate WHERE edate='$event_date';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbyedate (`edate`,`nr`) VALUES('$event_date','1')";
else $strsql = "UPDATE tblreportsbyedate SET nr=nr WHERE edate='$event_date';";
@mysql_query($strsql);

list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbystate WHERE state='$state';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbystate (`state`,`nr`) VALUES('$state','1')";
else $strsql = "UPDATE tblreportsbystate SET nr=nr WHERE state='$state';";
@mysql_query($strsql);

list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbyactivity WHERE activity='$activity';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbyactivity (`activity`,`nr`) VALUES('$activity','1')";
else $strsql = "UPDATE tblreportsbyactivity SET nr=nr WHERE activity='$activity';";
@mysql_query($strsql);

list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbycity WHERE city='$city';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbycity (`city`,`nr`) VALUES('$city','1')";
else $strsql = "UPDATE tblreportsbycity SET nr=nr WHERE city='$city';";
@mysql_query($strsql);

@mysql_close($dbconn);
?>

[tippy_102]

I don't know if it will correct your problem, but I don't think you need all those quotes. Try this idea:
[code]
$strsql = "update tblreports set event_date='$event_date', event_time='$event_time',
[/code]
I took the quotes off the [i]event_date[/i], but not the string.

[HuggieBear]

OK, let me give you a few pieces of advice before I tell you your problem...

1. Use [b][nobbc][code]...[/code][/nobbc][/b] tags around your code when posting in the forum as it looks a lot nicer and easy to read.

2. If you include <?php ?> tags in the code section you get syntax highlighting which is also a lot easier to read and also aids with spotting syntactically incorrect code.
[code]<?php
// Here's an example of what I mean by [code] tags and syntax highlighting
$var = "variable";
if (isset($var)){
  echo "You've set \$var to: " . $var;
}
?>[/code]

3. Don't nest your functions, as it's more difficult to read and report errors.  so avoid strings like this:
[code]$num = mysql_num_rows(mysql_query("SELECT column FROM table_name"));[/code]
in favour of:
[code]$sql = "SELECT column FROM table_name";
$result = mysql_query($sql) or die ("Couldn't execute $sql: " . mysql_error());
$num = mysql_num_rows($result);[/code]

4. Use UPPER CASE for MySQL command syntax as it makes things a lot easier to read.  So something like this:
[code]SELECT column_name FROM table_name WHERE column_name = 'condition'[/code]

This brings me onto solving your problem.  You don't have a WHERE clause in your MySQL statement.  Now this would have been a lot easier for me to spot if you'd capitalised your code :)

Basically you need to be saying something along the lines of...

[color=green][color=maroon]UPDATE[/color] tblreports [color=maroon]SET[/color] date_event = '$date_event' .... [b][color=maroon]WHERE[/color][/b] city = 'paris'[/color]

If you don't define a WHERE clause, the code will try to update every row in the table.

Regards
Huggie[/code]

[studgate]

Thanks HuggieBear for the help and suggestions
& tippy_102, I did try your suggestions but it didn't make any difference
What I wanted the query to do is to update the new
city or event_date or the new state or the new activity
and then delete the old one and replace the new one.
HuggieBear, I can't find where the WHERE clause was not set
I feel that there is a problem in this part of the code (it should add


Here is the update_report.php
[code]
<?php
<?
  $dbconn = @mysql_connect("localhost","user","passwordhere");
  @mysql_select_db("databasehere");
 
 
  $strsql = "UPDATE tblreports SET `event_date`='$event_date', `event_time`='$event_time', `kb`='$kb', `event_duration`='$event_duration', `city`='$city', `state`='$state', `zip`='$zip', `foreign_country`='$foreign_country', `county`='$county', `nr_witness`='$nr_witness', `activity`='$activity', `location_type`='$location_type', `location_age`='$location_age', `description`='$description', `summary`='$summary', `characteristics`='$characteristics', `uname`='$uname', `uemail`='$uemail', `uaddress`='$uaddress', `ucity`='$ucity', `ustate`='$ustate', `uzip`='$uzip', `uphone`='$uphone', `uoccupation`='$uoccupation', `religy`='$religy', `education`='$education', `uage`='$uage', `ugender`='$ugender', `medication`='$medication', `medication_recent`='$medication_recent', `illness`='$illness', `drug`='$drug', `drug_recent`='$drug_recent', `alcohol`='$alcohol', `alcohol_recent`='$alcohol_recent', `anyuseof`='$anyuseof', `charlast`='$charlast' WHERE id='$editid'";


  @mysql_query($strsql);
 
  list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbyedate WHERE edate='$event_date';"));
  if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbyedate (`edate`,`nr`) VALUES('$event_date','1')";
  else $strsql = "UPDATE tblreportsbyedate SET nr=nr WHERE edate='$event_date';";
  @mysql_query($strsql);
 
  list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbystate WHERE state='$state';"));
  if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbystate (`state`,`nr`) VALUES('$state','1')";
  else $strsql = "UPDATE tblreportsbystate SET nr=nr WHERE state='$state';";
  @mysql_query($strsql);
 
  list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbyactivity WHERE activity='$activity';"));
  if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbyactivity (`activity`,`nr`) VALUES('$activity','1')";
  else $strsql = "UPDATE tblreportsbyactivity SET nr=nr WHERE activity='$activity';";
  @mysql_query($strsql);
 
  list($nr_rows) = @mysql_fetch_row(@mysql_query("SELECT COUNT(*) FROM tblreportsbycity WHERE city='$city';"));
  if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbycity (`city`,`nr`) VALUES('$city','1')";
  else $strsql = "UPDATE tblreportsbycity SET nr=nr WHERE city='$city';";
  @mysql_query($strsql);
 
  @mysql_close($dbconn);
?> [/code]

[Destruction]

list($nr_rows) = @mysql_fetch_row

Firstly, why are you using list instead of just $nr_rows = @mysql_fetch_row etc.
Secondly, that will return an array, empty() is for checking if a string is empty.  An empty array, if I recall, will still return a string value of Array ().
Thirdly, SELECT COUNT(*) doesn't return a row, it returns a value/result.  You would be best using mysql_fetch_result($query, 0) perhaps.
Lastly, You are suppressing the error messages using @ but are not providing an alternative.  Without an alternative, you are not going to see what the actual error is.  That is a part of why nesting queries is a very bad idea, as well as the performance issues of using mysql_fetch_row regardless of whether mysql_query actually worked correctly etc.

Dest

[HuggieBear]

[quote author=studgate link=topic=124316.msg515473#msg515473 date=1170003438]
HuggieBear, I can't find where the WHERE clause was not set
[/quote]

Sorry, I didn't see it in the first post, even more reason to capitalise the syntax :)

There doesn't seem to be any syntax errors so try this and let me know if you get an error messages...

[code]
<?php
$dbconn = mysql_connect("localhost","user","passwordhere");
mysql_select_db("databasehere");
 
$strsql = "UPDATE tblreports SET `event_date`='$event_date', `event_time`='$event_time', `kb`='$kb', `event_duration`='$event_duration', `city`='$city', `state`='$state', `zip`='$zip', `foreign_country`='$foreign_country', `county`='$county', `nr_witness`='$nr_witness', `activity`='$activity', `location_type`='$location_type', `location_age`='$location_age', `description`='$description', `summary`='$summary', `characteristics`='$characteristics', `uname`='$uname', `uemail`='$uemail', `uaddress`='$uaddress', `ucity`='$ucity', `ustate`='$ustate', `uzip`='$uzip', `uphone`='$uphone', `uoccupation`='$uoccupation', `religy`='$religy', `education`='$education', `uage`='$uage', `ugender`='$ugender', `medication`='$medication', `medication_recent`='$medication_recent', `illness`='$illness', `drug`='$drug', `drug_recent`='$drug_recent', `alcohol`='$alcohol', `alcohol_recent`='$alcohol_recent', `anyuseof`='$anyuseof', `charlast`='$charlast' WHERE id='$editid'";

mysql_query($strsql) or die ("Unable to execute query:<br>$strsql<br>" . mysql_error());
 
list($nr_rows) = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM tblreportsbyedate WHERE edate='$event_date';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbyedate (`edate`,`nr`) VALUES('$event_date','1')";
else $strsql = "UPDATE tblreportsbyedate SET nr=nr WHERE edate='$event_date';";
mysql_query($strsql) or die ("Unable to execute query:<br>$strsql<br>" . mysql_error());

list($nr_rows) = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM tblreportsbystate WHERE state='$state';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbystate (`state`,`nr`) VALUES('$state','1')";
else $strsql = "UPDATE tblreportsbystate SET nr=nr WHERE state='$state';";
mysql_query($strsql) or die ("Unable to execute query:<br>$strsql<br>" . mysql_error());
 
list($nr_rows) = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM tblreportsbyactivity WHERE activity='$activity';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbyactivity (`activity`,`nr`) VALUES('$activity','1')";
else $strsql = "UPDATE tblreportsbyactivity SET nr=nr WHERE activity='$activity';";
mysql_query($strsql) or die ("Unable to execute query:<br>$strsql<br>" . mysql_error());
 
list($nr_rows) = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM tblreportsbycity WHERE city='$city';"));
if (empty($nr_rows)) $strsql = "INSERT INTO tblreportsbycity (`city`,`nr`) VALUES('$city','1')";
else $strsql = "UPDATE tblreportsbycity SET nr=nr WHERE city='$city';";
mysql_query($strsql) or die ("Unable to execute query:<br>$strsql<br>" . mysql_error());
 
mysql_close($dbconn);
?> [/code]

[studgate]

HuggieBear, i Didn't get an error message, and thanks for the help
Destruction , maybe a little example will help me a little more

basically what I wanted this code to do is to update my database
and to update the four tables (tblreportsbyedate, tblreportsbystate,
tblreportsbyactivity, & tblreportsbycity) for better sorting. It doesn't
seem to be doing that.
it does update my database but it is adding to the four tables without
removing the old ones.