Cant insert data into table but I can update data, dont know whats happenig

Question

this is my first question here and is really dumb.. but I cant get this workig in spite Ive done ir before, dunno if I take the bad pill or what, please help! Here is my code:

enter code here

<?php 
 session_start();
include '../conexion.php';

$nombre=$_POST['Nombre'];
$apellido=$_POST['Apellido'];
$mail=$_POST['Mail'];
$telefono=mysqli_real_escape_string($con,$_POST['Telefono']);
$ultimaventa=$_POST['Numeroventa'];
$totalcomprado=0;
$ultimomonto=$_POST['Total'];;
    $resultado=mysqli_query($con,"select * from Clientes")or       die(mysqli_error($con));
    $existe=false;
    while($f=mysqli_fetch_array($resultado)){
    if($f['Mail']==$mail){
        if($f['totalcomprado']==NULL){
                $totalcomprado=$ultimomonto;}else{$totalcomprado=$f['totalcomprado']+$ultimomonto;}

            mysqli_query($con,"update Clientes SET nombre='".$nombre."', apellido='".$apellido."',Mail='".$mail."',telefono='".$telefono."',ultimaventa='".$ultimaventa."',ultimomonto='".$ultimomonto."',totalcomprado='".$totalcomprado."'")or die(mysqli_error($con));

            }else{

                $totalcomprado=$ultimomonto;
                mysqli_query($con,"insert into clientes(nombre,apellido,Mail,telefono,ultimaventa,ultimomonto,totalcomprado)values(0,
                '".$nombre."','".$apellido."','".$mail."','".$telefono."','".$ultimaventa."','".$ultimomonto."','".$ultimomonto."')")or die(mysqli_error($con));}

        }

The problema is that the "update part"(when mail is already in database) everything works fine, but when I go to the insert statement nothing happens, nothing inserted, no mysql error, no nothing. All variables have proper values and all data is collected correctly, why i cant insert the data????PS: I tryed putting only the insert statement alone and ye nothing happens...

does it automatically commit the transaction? and are you sure it is getting to that code branch? — Randy, Feb 09 '15 at 23:29
Are you sure there's no error? Given that the code is *wide open* to SQL injection attacks that SQL statement could be doing *anything*. — David, Feb 09 '15 at 23:30
I think your table name is wrong. In your`UPDATE` query, you use "Clientes" with a capital "c". Try that. — JJJ, Feb 09 '15 at 23:32
No errors, but what about sql injections attacks?? whats that, where is the flaw, by the way, its still not working... =( — Lucho Mansilla, Feb 09 '15 at 23:36
Josan, I checked that and i havent realized it, but the mistery is that update is working, insert not working, and the table name is not with capital C, I dont get it — Lucho Mansilla, Feb 09 '15 at 23:38
**Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. Running SQL code built with outside data is like eating soup made from ingredients found on your doorstep. — Andy Lester, Feb 09 '15 at 23:40

Ishamael · Answer 1 · 2015-02-09T23:47:33.170

Your INSERT query has 7 columns in the fields clause, but provides 8 values (note that you have an extra 0 at the beginning of the list of values, which doesn't have a matching column name in the list of fields). It certainly produces an error, you are just not properly catching it.

To quickly fix it, just remove the 0, part in the VALUES clause, but I would recommend figuring out why the error is not seen.

EDIT: and as Josan Iracheta properly pointed out, in MySQL table names are case sensitive, so your table name in the INSERT query needs to begin with a capital letter too.

EDIT2: to be very specific, try this:

mysqli_query($con,"insert into Clientes(nombre,apellido,Mail,telefono,ultimaventa,ultimomonto,totalcomprado)values(
            '".$nombre."','".$apellido."','".$mail."','".$telefono."','".$ultimaventa."','".$ultimomonto."','".$ultimomonto."')")or die(mysqli_error($con));}

Also, please note that you have several other problems in your code: your code is vulnerable to SQL injections (try using prepared statements to address it), and also your update query doesn't have WHERE clause, so you update all the rows every time, not just the one that has matching email address.

EDIT4: Now that I looked at your code more closely, your problem not in SQL, it is in PHP -- your logic for running the INSERT query seems to be wrong, you run it if your table has a row with a different email, not if it doesn't have a row with the email you want. Try changing your code like this:

<?php
session_start();
include '../conexion.php';

$nombre=$_POST['Nombre'];
$apellido=$_POST['Apellido'];
$mail=$_POST['Mail'];
$telefono=mysqli_real_escape_string($con,$_POST['Telefono']);
$ultimaventa=$_POST['Numeroventa'];
$totalcomprado=0;
$ultimomonto=$_POST['Total'];;
$resultado=mysqli_query($con,"select * from Clientes WHERE Mail='".$mail."'")or       die(mysqli_error($con));
$existe=false;

if (mysqli_num_rows($resultado) == 0) {
    $totalcomprado=$ultimomonto;
    mysqli_query($con,"insert into clientes(nombre,apellido,Mail,telefono,ultimaventa,ultimomonto,totalcomprado)values(
        '".$nombre."','".$apellido."','".$mail."','".$telefono."','".$ultimaventa."','".$ultimomonto."','".$ultimomonto."')")or die(mysqli_error($con));
}

while($f=mysqli_fetch_array($resultado)){
    if($f['Mail']==$mail){
        if($f['totalcomprado']==NULL){
            $totalcomprado=$ultimomonto;}else{$totalcomprado=$f['totalcomprado']+$ultimomonto;}

        mysqli_query($con,"update Clientes SET nombre='".$nombre."', apellido='".$apellido."',Mail='".$mail."',telefono='".$telefono."',ultimaventa='".$ultimaventa."',ultimomonto='".$ultimomonto."',totalcomprado='".$totalcomprado."' WHERE Mail='".$mail."'")or die(mysqli_error($con));

    }

}

Note that I also added the WHERE clause to the SELECT and UPDATE statements, remove them if it is not what you actually want there. I also did not address all the SQL-injection issues in your code.

The 0 value is to generate the auto incrementing id i have in table, it desnt works works with or without it — Lucho Mansilla, Feb 09 '15 at 23:35
Then you need to specify the column at the beginning of the fields list. Number of elements in the two lists should always be the same. But omitting the column in both field list and values list should also work fine for the auto increment. What is your auto_increment column? It's not nombre, is it? If it is, remove `$nombre` from the values clause. — Ishamael, Feb 09 '15 at 23:37
Ishamael the auto increment is id, I tryed putting C capital and C not capital in both querys, not working, the rare thing is that before I noticed the Capital C problema, the update statemente was working, but my table name is clientes, without capital C — Lucho Mansilla, Feb 09 '15 at 23:42
Great! its working! so, u have to check if there is no rows with that mail and insert, then the if statement , thanks guysssss, love u, love this page, sorrry about this, i am new in this, c u soon! — Lucho Mansilla, Feb 09 '15 at 23:55
I dont get about the sql injections part yet, but I think this is for other question if i cant find out myselg, thanks again — Lucho Mansilla, Feb 09 '15 at 23:56
ONE LAST THING, name for table is clientes not Clientes, but it is working with the capital not working when I use clientess....... — Lucho Mansilla, Feb 09 '15 at 23:59
How do you know it is with lower case 'c'? Try running `SHOW TABLES` in MySQL console and see what is the spelling there. — Ishamael, Feb 10 '15 at 00:04
I don't have any further ideas. I would try to figure out why your MySQL errors to not properly show up, it will help you with debugging a lot. Try creating a very small PHP file with a query which is certainly wrong and make sure you can see the error. — Ishamael, Feb 10 '15 at 00:39

Cant insert data into table but I can update data, dont know whats happenig

1 Answers1