0

so my newest problem! i have a save button and that save button already has the insert into query and working fine now what i wanna do is when the user clicks on the save button what it does first is, it checks if a record of that user already exists and if there already is a record in regards to that user then instead of inserting it will update the table. but before updating i want a confirmation message that asks the user if they would like to update because there already is a record. and if there isnt a record then it will just insert. ive already googled things like "check if record exist in mysql db" and "Redirect to other page on alert box confirm" and got the coding (just had to tweek it to make it work with mine) but to combine the whole thing im very sure im not doing it right as i have files calling other files(im pretty certain theres a better way than mine, always is).

what i have so far:

saveRecord.php

<?php
session_start();
if($_SESSION['name']){
if(isset($_POST['save']))
{
    $ahouse = $_POST['ahouse'];
    $avehicle = $_POST['avehicle'];
    $acurrent = $_POST['acurrent'];
    $asaving = $_POST['asavings'];
    $akwsp = $_POST['akwsp'];
    $ahaji = $_POST['ahaji'];
    $aasb = $_POST['aasb'];
    $astock = $_POST['astock'];
    $aproperty = $_POST['aproperty'];
    $aothers = $_POST['aothers'];
    $total = $_POST['assets'];

    require "connect.php";
    $name=$_SESSION['name'];
    $check=mysql_query("SELECT COUNT(*) FROM `asset` WHERE name = '$name' LIMIT 1");
    if(!$check)
    {
        die('Invalid query: ' .mysql_error());
    }

    if(mysql_num_rows($check) == 0)
    {
        $name=$_SESSION['name'];
        $query1=mysql_query("INSERT INTO asset(name,house,vehicle,current,savings,kwsp,haji,asb,stock,property,others,total) VALUES ('$name','$ahouse','$avehicle','$acurrent','$asaving','$akwsp','$ahaji','$aasb','$astock','$aproperty','$aothers','$total')");  
        if(!$query1)
        {
            die('Invalid query: ' .mysql_error());
        }
        else
        {
            echo ('<script>alert("Make sure you fill in your liability sheet!")
            window.location="asset.php"</script>');
        }
    }
    else
    {
        echo ('<script type="text/javascrit">
            function deletePost() 
            {
                var ask = window.confirm("You have already filled in this form. Would you like to update?");
                if (ask) 
                {
                    document.location.href = "update.php";
                }
            }
            <script>');
    }

}

i had also used SELECT name FROM asset WHERE name = '$name' LIMIT 1 instead of COUNT(asterik) because i read that using COUNT isnt good but in this code im showing you guys it uses count coz i changed it.

update.php (i also think this file is messed up and i probably dont need this file but i didn't know how to go about doing this whole coding to achieve what i want)

<?php
session_start();
if($_SESSION['name'])
{
    if(isset($_POST['save']))
    {
        $ahouse = $_POST['ahouse'];
        $avehicle = $_POST['avehicle'];
        $acurrent = $_POST['acurrent'];
        $asaving = $_POST['asavings'];
        $akwsp = $_POST['akwsp'];
        $ahaji = $_POST['ahaji'];
        $aasb = $_POST['aasb'];
        $astock = $_POST['astock'];
        $aproperty = $_POST['aproperty'];
        $aothers = $_POST['aothers'];
        $total = $_POST['assets'];

        require "connect.php";
        $name=$_SESSION['name'];
        $update=mysql_query("UPDATE asset SET house='$ahouse', vehicle='$avehicle', current='$acurrent', savings='$asaving', kwsp='$akwsp',
        haji='$ahaji', asb='$aasb', stock='$astock', property='$aproperty', others='$aothers', total='assets' WHERE name='$name'");

        if($update)
        {
            echo ('<script>alert("Your asset values have been updated")
                    header("Location : saveRecord.php");</script>');
        }
    }
}   
?>

thanks in advance

4
Contributors
14
Replies
107
Views
3 Years
Discussion Span
Last Post by diafol
Featured Replies
  • 2

    Actually, you can do this in a single query: INSERT INTO asset (name, house, vehicle, current, savings, kwsp, haji, asb, stock, property, others, total) VALUES ('$name', '$ahouse', '$avehicle', '$acurrent', '$asaving', '$akwsp', '$ahaji', '$aasb', '$astock', '$aproperty', '$aothers', '$total') ON DUPLICATE KEY UPDATE house = '$ahouse', vehicle = '$avehicle', current = '$acurrent', … Read More

0

There is some ways to do this, but I have this for you:

$conn = mysql_connect ( $host, $username, $pswd );
$select_DB = mysql_select_db($db);
$check_permission = 'SELECT * FROM asset';
$check_query = mysql_query( $check_permission, $conn );
        while ( $record = mysql_fetch_assoc( $check_query ) ) {
                if ( ($record['name'] == $name) && ($record['house'] == $ahouse) &&
                       ($record['vehicle'] == $avehicle) && ($record['current'] == $acurrent) ) {// you can add extra with this style. either you can use || (or) instead of &&(and)
                    $permission = false;
                }
                else {
                    $permission = true; 
                }
        }
if ( $permission ) {
    //Insert
}
else {
    //Update
}

also I suggest you to learn about PDO, because PDO is more flexible and safe...
I hope to be helpful for you

2

Actually, you can do this in a single query:

INSERT INTO asset 
(name, house, vehicle, current, savings, kwsp, haji, asb, stock, property, others, total) 
VALUES ('$name', '$ahouse', '$avehicle', '$acurrent', '$asaving', '$akwsp', '$ahaji', '$aasb', '$astock', '$aproperty', '$aothers', '$total')
ON DUPLICATE KEY 
UPDATE house = '$ahouse', vehicle = '$avehicle', current = '$acurrent', savings = '$asaving', kwsp = '$akwsp', haji = '$ahaji', asb = '$aasb', stock = '$astock', property = '$aproperty', others = '$aothers', total = 'assets' 

The above works if there is an identifiable primary key.

0

im trying phpkoder's code but im getting this error

Notice: Undefined variable: permission in C:\xampp\htdocs\CashFlow\sheets.php on line 38

the code:

if(isset($_POST['save']))
{
    $ahouse = $_POST['ahouse'];
    $avehicle = $_POST['avehicle'];
    $acurrent = $_POST['acurrent'];
    $asaving = $_POST['asavings'];
    $akwsp = $_POST['akwsp'];
    $ahaji = $_POST['ahaji'];
    $aasb = $_POST['aasb'];
    $astock = $_POST['astock'];
    $aproperty = $_POST['aproperty'];
    $aothers = $_POST['aothers'];
    $total = $_POST['assets'];

    require "connect.php";
    $name=$_SESSION['name'];
    $check_permission='SELECT * FROM asset';
    $check_query=mysql_query($check_permission);

    while($record=mysql_fetch_assoc($check_query))
    {
        if( ($record['name'] == $name)&&($record['house'] == $ahouse)&&($record['vehicle'] == $avehicle)&&($record['current'] == $acurrent)&&
        ($record['savings'] == $asaving)&&($record['kwsp'] == $akwsp)&&($record['haji'] == $ahaji)&&($record['asb'] == $aasb)&&($record['stock'] == $astock)
        &&($record['property'] == $astock)&&($record['others'] == $aothers)&&($record['total'] == $total) )
        {
            $permission = false;
        }
        else
        {
            $permission =true;
        }
    }

    if($permission)  **<--this line**
    {
        $query1=mysql_query("INSERT INTO asset VALUES ('$name','$ahouse','$avehicle','$acurrent','$asaving','$akwsp','$ahaji','$aasb','$astock','$aproperty','$aothers','$total')");    
        if($query1)
        {
            echo ('<script>alert("Make sure you fill in your liability sheet!")
            window.location="sheet.php"</script>');
        }
        else
        {echo "can't insert";}
    }
    else
    {
        $query2=mysql_query("UPDATE asset SET house='$ahouse',vehicle='$avehicle',current='$acurrent',savings='$asaving',kwsp='$akwsp',haji='$ahaji',asb='$aasb',stock='$astock',property='$aproperty',others='$aothers',total='$total' WHERE name = '$name' ");
        if($query2)
        {
            echo ('<script>alert("Your Asset Sheet has been updated!")
            window.location="sheet.php"</script>');
        }
        else
        {echo "can't update";}
    }   
}
0

write this line, before the while loop:
$permission;
and if it returned error again; set the permission variable to false by default and use this:
$permission = false;

Edited by phpkoder: for more description

0

$permission=false doesnt give me error but it goes straight to this code and nothing in the database table.]

 else
    {
        $query2=mysql_query("UPDATE asset SET house='$ahouse',vehicle='$avehicle',current='$acurrent',savings='$asaving',kwsp='$akwsp',haji='$ahaji',asb='$aasb',stock='$astock',property='$aproperty',others='$aothers',total='$total' WHERE name = '$name' ");
        if($query2)
        {
            echo ('<script>alert("Your Asset Sheet has been updated!")
            window.location="sheet.php"</script>');
        }

$permission gives me the same error undefined.... but also goes to the coding above.

0

You're using a while loop because there are multiple records? If so, then the $permissions var is being overwritten on each iteration, so you just get the last value. If not, then you shouldn't need a loop. Just an observation.

Add an or die(mysql_error()) to your queries.

0

i did this:

session_start();
if($_SESSION['name']){
if(isset($_POST['save']))
{
    $ahouse = $_POST['ahouse'];
    $avehicle = $_POST['avehicle'];
    $acurrent = $_POST['acurrent'];
    $asaving = $_POST['asavings'];
    $akwsp = $_POST['akwsp'];
    $ahaji = $_POST['ahaji'];
    $aasb = $_POST['aasb'];
    $astock = $_POST['astock'];
    $aproperty = $_POST['aproperty'];
    $aothers = $_POST['aothers'];
    $total = $_POST['assets'];

    require "connect.php";
    $name=$_SESSION['name'];

    $check=mysql_query("SELECT count(*) FROM asset WHERE name = '$name' ");

    if($check)
    {
        echo ('<script>alert("Your record will be updated"</script>');
        $query2=mysql_query("UPDATE asset SET house='$ahouse',vehicle='$avehicle',current='$acurrent',savings='$asaving',kwsp='$akwsp',haji='$ahaji',asb='$aasb',stock='$astock',property='$aproperty',others='$aothers',total='$total' WHERE name = '$name' ") or die(mysql_error());
        if($query2)
        {
            echo ('<script>alert("Your Asset Sheet has been updated!")
            window.location="sheet.php"</script>');
        }
        else
        {   echo "can't update";    }
    }

    if(!$check)
    {
        $query1=mysql_query("INSERT INTO asset VALUES ('$name','$ahouse','$avehicle','$acurrent','$asaving','$akwsp','$ahaji','$aasb','$astock','$aproperty','$aothers','$total')") or die(mysql_error());
        if(!$query1)
        {echo "can't insert";}
    }   

}

but only if($check) runs but database table is empty.

0

instead of if($check) check the number of rows. if(mysql_num_rows($check))

0

Saying "it's not working" is no help at all. Can you provide additional info. I have no idea what your current code looks like. Try just using an echo instead of those horrible alerts for now and place them at strategic points.

Anyway, for insert and update, again don't use something like if($query), you'll want to use mysql_affected_rows()

In fact pritaeas gave you a neat solution in the ON DUPLICATE KEY example. Why haven't you tried that?

0

the current code is still the same as the one one i had recently posted. the only changes i made is the one you suggested. I want to be able to have confirmation before the record can be updated and im not sure if i can do that with ON DUPLICATE KEY. maybe ill just change how my page works so i can use
ON DUPLICATE KEY instead.

0

I want to be able to have confirmation before the record can be updated and im not sure if i can do that with ON DUPLICATE KEY

If you want confirmation then the ON DUPLICATE is not what you want.

0

Other than picking apart stuff I can't possibly say if they are set or not (e.g. field values), here are a quick couple of tests:

//change id below to your PK field
$sql = "SELECT id FROM asset WHERE name = '$name' LIMIT 1";
$check = mysql_query($sql) or die(mysql_error());
echo "SELECT QUERY: $sql <br />";
if(mysql_num_rows($check))
{
    $sql = "UPDATE asset SET house='$ahouse',vehicle='$avehicle',current='$acurrent',savings='$asaving',kwsp='$akwsp',haji='$ahaji',asb='$aasb',stock='$astock',property='$aproperty',others='$aothers',total='$total' WHERE name = '$name' ";
    $query2 = mysql_query($sql) or die(mysql_error());
    echo "UPDATE QUERY: $sql <br />";
    if(mysql_affected_rows())
    {
        echo "Updated<br />";
        /*echo ('<script>alert("Your Asset Sheet has been updated!")
        window.location="sheet.php"</script>');*/
    }
    else
    {   
        echo "Can't Update<br />";    
    }
}
else
{
    $sql = "INSERT INTO asset VALUES ('$name','$ahouse','$avehicle','$acurrent','$asaving','$akwsp','$ahaji','$aasb','$astock','$aproperty','$aothers','$total')";
    $query1 = mysql_query($sql) or die(mysql_error());
    echo "INSERT QUERY: $sql <br />";
    if(!mysql_affected_rows())
    {
        echo "Can't Insert";
    }else{
        echo "Inserted";    
    }
}
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.