Created a new table, where there will be a list of users and their points. I make a record in the table in this way

global $DB; $DB->Query("update rub_guess_photo set POINTS=POINTS+0.02 where USER_ID='$userid' limit 1;");

I derive in this way:

 global $DB; $DB->Query("SELECT USER_ID FROM rub_guess_photo ORDER BY POINTS DESC LIMIT 10"); while ($row = mysql_fetch_array($sql)) { print_r($row[1]); }

But the record for some reason is not added and not displayed. I manually created an entry in this table, tried to print - empty. Where is the error in the first and second case?

  • one
    Well, in order: 1. SQL statements are written with a capital letter. 2. Database fields are written in the pole 3. And remove the semicolon at the end of the first query - systemiv
  • > 1. SQL statements are written with a capital letter. This is not a requirement, but a tradition. You can write and small. - Ali

2 answers 2

Adding entries happens like this:

 INSERT INTO tabe1 (field1, field1) values ('value1','value2');

UPDATE is an update.

Here is an example of adding and output:

 <?php $connect = mysql_connect('localhost','root','12345'); mysql_select_db('08ist'); $name=strip_tags($_POST["name"]); $data=strip_tags($_POST["data_rojd"]); $sql = "INSERT INTO student (name, data_rojdeniya) values ('$name','$data');"; mysql_query($sql,$connect); $sql="SELECT * FROM student"; $result=mysql_query($sql,$connect); print "<table border=1>\n"; print "<tr>\n"; while ($field = mysql_fetch_field($result)) { print "<th>$field->name</th>\n"; } print "</tr>\n\n"; while($row = mysql_fetch_assoc($result)) { print "<tr>\n"; foreach ($row as $col=>$val) { print "<td>$val</td>\n"; } print "</tr>\n\n"; } print "</table>\n"; ?>

Check whether there is such an entry in the table or not, you can thus:

 $sql="SELECT name,data_rojdeniya FROM student WHERE name='$name' AND data_rojdeniya='$data';"; $result=mysql_query($sql,$connect); $num_rows = mysql_num_rows($result); if($num_rows>0) { echo "Такой есть";//в вашем случае обновляем } else { //добавляем }
  • When then check if the user has $ userid entry to know to update or create an entry? - Tchort
  • one
    Well, make a request to the database, and if the request returns empty lines, then you need to add, and otherwise update) I corrected in the answer) - Leshij_2005
  • Do not forget to accept the answer, or vote) Thank you) - Leshij_2005

6.4.8 Operator syntax REPLACE

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES (expression,...),(...),... или REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ... или REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name=expression, col_name=expression,...

The REPLACE statement works in the same way as INSERT, except that if the old entry in this table has the same UNIQUE or PRIMARY KEY index value as the new one, then the old entry will be deleted before the entry of the new one. See section 6.4.3 INSERT statement syntax.

In other words, the REPLACE command does not provide access to the replaced record. In some older versions of MySQL, such access was sometimes possible, but it was a defect that has already been fixed.

When using the REPLACE command, mysql_affected_rows () will return a value of 2 if the old line has been replaced with a new one. This is explained by the fact that one row is inserted into the table first, and then the second is removed - a duplicate.

This makes it easy to determine which action was performed by the REPLACE command — adding or replacing a string. It is enough just to check what number the mysql_affected_rows () function returned - 1 (line added) or 2 (replaced).

It should be noted that if UNIQUE or PRIMARY KEY indices are not used, the use of the REPLACE command does not make sense, since it works simply as an INSERT.

Source of

Add will remove duplicate in one request.