The second query is not executing in php mysql

Question

I'm newbie in PHP and WordPress. This approach was working fine for me in ASP.NET but here both queries are not working. When I comment the first one, the second one(Insertion) is working fine.

$dbhostname="111.1.11.111"; 
$dbusername="db_userName";
$dbpassword="mypassword";

$con=mysqli_connect($dbhostname,$dbusername,$dbpassword,"db_name");

if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

Check wether the email exists or not ?

$sql="CALL Select_ConfirmEmailExistance('[email protected]')";

  $containsResult=0;
 if ($result=mysqli_query($con,$sql))
 {
 // Get field information for all fields
 while ($fieldinfo=mysqli_fetch_assoc($result))
 {
    if (isset($fieldinfo)) {
        $containsResult=1;// Email Exists 
    }
}
mysqli_free_result($result);

if ($containsResult==0) { // In case email does not exists enter it.

    $sql="CALL insert_Userinfo('abc','def','[email protected]','mnop')";
    if ($result=mysqli_query($con,$sql))
    {
        $data;
        while ($fieldinfo=mysqli_fetch_assoc($result))
        {
            $data[]=$fieldinfo;
        }
    }
  }
  print_r($data);
  }

mysqli_close($con);

First Store Procdure

BEGIN
    SELECT 1 as emailstatus FROM userinfo WHERE email= p_email;
END

Second Stored Procedure

INSERT INTO `userinfo` (
    `first_name`,
    `last_name`,
    `email`,
    `password`
)
VALUES
    (
        `FName`,
        `LName`,
        `Email`,
        `Pass`
    );

SELECT
    user_id
FROM
    userinfo
ORDER BY
    user_id DESC
LIMIT 1;

@pr1nc3 the database which i'm calling is not the wordpress database. that is other. — Abdul Qayyum
– Abdul Qayyum, Commented Oct 15, 2014 at 5:10
@BhumiShah the store procedure's are working fine.any how i'm sharing the store procedures. when i'm using one query in at a time the both queries are working fine,it creates problem when i'm calling both at a time. — Abdul Qayyum
– Abdul Qayyum, Commented Oct 15, 2014 at 5:26
Why not create a function or class/method to return your queries? They are self-contained and are less likely to conflict when you are using the same variables in each call. — Rasclatt
– Rasclatt, Commented Oct 15, 2014 at 5:28

Rasclatt · Accepted Answer · 2014-10-15 06:09:57Z

2

Here is what I was talking about when I said create a query class to fetch data. This is just a simple one, but it works pretty effectively and you can build it out to be pretty powerful.

class   DBEngine
    {
        public  $con;
        public  function __construct($host="111.1.11.111",$db = "dbname",$user="db_userName",$pass="mypassword")
            {
                try {
                        $this->con  =   new PDO("mysql:host=$host;dbname=$db",$user,$pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
                    }
                catch (Exception $e) {
                      return 0;
                    }
            }

        // Simple fetch and return method
        public  function Fetch($_sql)
            {
                $query  =   $this->con->prepare($_sql);
                $query->execute();

                if($query->rowCount() > 0) {
                        while($rows = $query->fetch(PDO::FETCH_ASSOC)) {
                                $array[]    =   $rows;
                            }
                    }

                return (isset($array) && $array !== 0 && !empty($array))? $array: 0;
            }

        // Simple write to db method
        public  function Write($_sql)
            {
                $query  =   $this->con->prepare($_sql);
                $query->execute();
            }
    }

// Create an instance of the engine
$query          =   new DBEngine();
// Query 1 will return an array or false (0)
$call1          =   $query->Fetch("CALL Select_ConfirmEmailExistance('[email protected]')");
// Assign your true/false
$containsResult =   ($call1 !== 0)? 1:0;
// Run second query and return array or false (0)
if($containsResult == 0)
    $data   =   $query->Fetch("CALL insert_Userinfo('abc','def','[email protected]','mnop')");
// Display returned result
print_r($data);

edited Oct 15, 2014 at 6:09

answered Oct 15, 2014 at 5:48

Rasclatt

12.5k3 gold badges28 silver badges33 bronze badges

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

2 Comments

Abdul Qayyum Over a year ago

your code is pretty elaborate. really appreciate that. thank you very much for such help.

Rasclatt Over a year ago

No problem. Actually, it only looks elaborate at first glance. If you put the DBEngine class in your config file or where ever you are housing your database connection, you can run them simultaneously if you want. Just test it out and I guarantee you will like it! Try a simple select * from whatevertable sql and see how it runs.

Aych Jay · Accepted Answer · 2014-10-15 05:46:49Z

1

It is quite simple. Your code is fine but you only have to create two separete functions and simply call those functions instead of the code directly.

answered Oct 15, 2014 at 5:46

Aych Jay

486 bronze badges

Collectives™ on Stack Overflow

The second query is not executing in php mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related