cannot create database using mySQLi

Question

I'm always getting error when inserting the value to my database. but it's working when i'm using mySQL only, I'm new to SQLi so please be gentel.

insert.php

    <?php
    include("dbinfo.inc.php");

    //connect to mysql server
    $mysqli = new mysqli("localhost", $username, $password, $database);

    //check if any connection error was encountered
    if(mysqli_connect_errno()) {
        echo "Error: Could not connect to database.";
        exit;
    }     

    //insert    
    $query = "insert into client_info set (
    id = '".$mysqli->real_escape_string('')."',
    first = '".$mysqli->real_escape_string($_POST['first'])."',
    last = '".$mysqli->real_escape_string($_POST['last'])."',
    phone = '".$mysqli->real_escape_string($_POST['phone'])."',
    mob = '".$mysqli->real_escape_string($_POST['mob'])."',
    fax = '".$mysqli->real_escape_string($_POST['fax'])."',
    email = '".$mysqli->real_escape_string($_POST['email'])."',
    web = '".$mysqli->real_escape_string($_POST['web'])."',
    com = '".$mysqli->real_escape_string($_POST['com'])."',
    address = '".$mysqli->real_escape_string($_POST['address'])."',
    city = '".$mysqli->real_escape_string($_POST['city'])."',
    state = '".$mysqli->real_escape_string($_POST['state'])."',
    zip = '".$mysqli->real_escape_string($_POST['zip'])."',
    zone = '".$mysqli->real_escape_string($_POST['zone'])."',
    office = '".$mysqli->real_escape_string($_POST['office'])."',
    office_num = '".$mysqli->real_escape_string($_POST['office_num'])."',
    ext_mob = '".$mysqli->real_escape_string($_POST['ext_mob'])."',
    ext_phone = '".$mysqli->real_escape_string($_POST['ext_phone'])."',
    ext_office = '".$mysqli->real_escape_string($_POST['ext_office'])."',
    srv = '".$mysqli->real_escape_string($_POST['srv'])."',
    stype = '".$mysqli->real_escape_string($_POST['stype'])."',
    voip = '".$mysqli->real_escape_string($_POST['voip'])."',
    vpass = '".$mysqli->real_escape_string($_POST['vpass'])."',
    regDate = '".$mysqli->real_escape_string($_POST['regDate'])."',
    acct = '".$mysqli->real_escape_string($_POST['acct'])."',
    Nagents = '".$mysqli->real_escape_string($_POST['Nagents'])."',
    agents = '".$mysqli->real_escape_string($_POST['agents'])."',
    password = '".$mysqli->real_escape_string($_POST['password'])."'
    )";

        //execute the query
        if( $mysqli->query($query) ) {
            //if saving success
            echo "User was created.";
        }else{
            //if unable to create new record
            echo "Database Error: Unable to create record.";
        }
        //close database connection
        $mysqli->close();


    ?>

create.php

<html>
<head>
<title></title>
<script language="JavaScript">
var today=new Date();
var jran=today.getTime();

function random() {
    ia=9301;
    ic=49297;
    im=233280;
    jran = (jran*ia+ic) % im;
    return jran/(im*1.0);
};

function rand(number) {
    return Math.ceil(random()*number);
};

function makearray(n) {
    this.length = n;
    for (var i = 1; i <= n; i++) this[i] = 0;
    return this;
}

var asciitable = new makearray (128);
asciitable.length=128;
for (var i=0;i<=127;i++) asciitable[i]="";
asciitable[33]="!"; asciitable[34]="\""; 
asciitable[35]="#"; asciitable[36]="$";
asciitable[37]="%"; asciitable[38]="&"; 
asciitable[39]="'"; asciitable[40]="(";
asciitable[41]=")"; asciitable[42]="*"; 
asciitable[43]="+"; asciitable[44]=",";
asciitable[45]="-"; asciitable[46]="."; 
asciitable[47]="/"; asciitable[48]="0";
asciitable[49]="1"; asciitable[50]="2"; 
asciitable[51]="3"; asciitable[52]="4";
asciitable[53]="5"; asciitable[54]="6"; 
asciitable[55]="7"; asciitable[56]="8";
asciitable[57]="9"; asciitable[58]=":"; 
asciitable[59]=";"; asciitable[60]="<";
asciitable[61]="="; asciitable[62]=">"; 
asciitable[63]="?"; asciitable[64]="@";
asciitable[65]="A"; asciitable[66]="B"; 
asciitable[67]="C"; asciitable[68]="D";
asciitable[69]="E"; asciitable[70]="F"; 
asciitable[71]="G"; asciitable[72]="H";
asciitable[73]="I"; asciitable[74]="J"; 
asciitable[75]="K"; asciitable[76]="L";
asciitable[77]="M"; asciitable[78]="N"; 
asciitable[79]="O"; asciitable[80]="P";
asciitable[81]="Q"; asciitable[82]="R"; 
asciitable[83]="S"; asciitable[84]="T";
asciitable[85]="U"; asciitable[86]="V"; 
asciitable[87]="W"; asciitable[88]="X";
asciitable[89]="Y"; asciitable[90]="Z"; 
asciitable[91]="["; asciitable[92]="\\";
asciitable[93]="]"; asciitable[94]="^"; 
asciitable[95]="_"; asciitable[96]="`";
asciitable[97]="a"; asciitable[98]="b"; 
asciitable[99]="c"; asciitable[100]="d";
asciitable[101]="e"; asciitable[102]="f"; 
asciitable[103]="g"; asciitable[104]="h";
asciitable[105]="i"; asciitable[106]="j"; 
asciitable[107]="k"; asciitable[108]="l";
asciitable[109]="m"; asciitable[110]="n"; 
asciitable[111]="o"; asciitable[112]="p";
asciitable[113]="q"; asciitable[114]="r"; 
asciitable[115]="s"; asciitable[116]="t";
asciitable[117]="u"; asciitable[118]="v"; 
asciitable[119]="w"; asciitable[120]="x";
asciitable[121]="y"; asciitable[122]="z"; 
asciitable[123]="{"; asciitable[124]="|";
asciitable[125]="}"; asciitable[126]="~";

function nchar(num) {
    if ((num>=33) && (num<=127)) return asciitable[num];
}

function doit() {
    var i;
    var n;
    var s = "";
            for (i=1;i<=8;i++) {
                n=0;
                while ( (n<=47) || 
                                        ((n>=58) && (n<=96)) || 
                                        (n>=123))  n = rand(126);
                s = s + nchar(n);
            }

    document.gen.password.value = s;
}
</script>
</head>

<body>

<form method="post" action="insert.php" name="gen">
<b>Personal Info:</b>
<p>First Name:<input type="text" name="first" size="20" /></p>
<p>Last Name:<input type="text" name="last" size="20" /></p>
<p>Mob:<input type="text" name="mob" size="20" />
ext:<input type="text" name="ext_mob" size="4" /></p>
<p>Phone:<input type="text" name="phone" size="20" />
ext:<input type="text" name="ext_phone" size="4" /></p>
<p>Fax:<input type="text" name="fax" size="20" /></p>
<p>E-mail:<input type="text" name="email" size="35" />
<p>Address:<input type="text" name="address" size="40" /></p>
<p>City:<input type="text" name="city" size="20" /></p>
<p>State:<input type="text" name="state" size="20" /></p>
<p>Zip Code:<input type="text" name="zip" size="5" /></p>
<p>Zone:<input type="text" name="zone" size="5" /></p>
<br>

<b>Office Info:</b>
<p>Company:<input type="text" name="com" size="40" /></p>
<p>Office Address:<input type="text" name="office" size="40" /></p>
<p>Office Num #:<input type="text" name="office_num" size="15" />
ext:<input type="text" name="ext_office" size="4" /></p>
<p>Website:<input type="text" name="web" size="30" /></p><br>
<b>Account Info:</b>
<p>Registry Date:<input type="text" name="regDate" size="30" /></p>
<p>Account:<input type="text" name="acct" size="20" /></p>
<p>Service:<input type="text" name="srv" size="15" /></p>
<p>Service Type:<input type="text" name="stype" size="40" /></p>
<p>Number of Agents:<input type="text" name="Nagents" size="3" /></p><br>


<b>Agent Info:</b>
<p>Agents:<input type="text" name="agents" size="40" /></p>
<p>VOID:<input type="text" name="voip" size="20" /></p>
<p>VOIP Password:<input type="text" name="vpass" size="20" /></p>
<br>

<input type="hidden" size="20" name="password">

<input type="submit" name="go" value="Insert to Database" onclick="doit()">
</form>




</body>
</html>

the error i get is when the scripts tries to insert the query to the DB

"Database Error: Unable to create record."

i tried to see if there are any output on my form, and there is, even tried to insert just one variable "first" still getting errors, don't know what I'm doing wrong here

Use $mysqli->error to get the actual error message instead of echoing your error message. — air4x
– air4x, Commented Oct 29, 2012 at 19:20

dev-null-dweller · Accepted Answer · 2012-10-29 19:18:43Z

3

If you are using SET syntax in INSERT statement, you should not use parenthesis around it.

-- One way to insert
INSERT INTO table SET col1 = 'val1', col2 = 'val2';

-- other way
INSERT INTO table(col1,col2) VALUES('val1','val2');

Also use native errors provided by the driver to see what's wrong with your query

answered Oct 29, 2012 at 19:18

dev-null-dweller

29.5k3 gold badges68 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

cannot create database using mySQLi

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related