Jump to content



Photo

Is There Any Problems Wit This Query?


  • Please log in to reply
19 replies to this topic

#1 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 3 May 2012 - 12:00 PM

Hi,

I have this query and it keeps giving me Fatal error: Call to a member function bind_param() on a non-object.
The name of the fields are correct and the connetions is set properly.



$sql ="SELECT UserID From user WHERE UserID != ? AND Email = ? ";

//prepare stmt
$stmt = $mysqli->prepare($sql);

//bind param
$stmt->bind_param('is',$ID, $Email);

//execute the query
$stmt->execute();
  • 0

#2 HartleySan

HartleySan

    Advanced Member

  • Members
  • PipPipPip
  • 2,932 posts
  • LocationColumbus, OH USA

Posted 3 May 2012 - 12:52 PM

The "not equal" operator in SQL is "<>", not "!=".
  • 0

#3 Edward

Edward

    Advanced Member

  • Members
  • PipPipPip
  • 1,128 posts

Posted 3 May 2012 - 12:53 PM

I just run this query on my users database with the mysql phpmyadmin

SELECT user_id FROM users WHERE user_id != 2 AND email = 'example@email.com'

I got success with it

Showing rows 0 - 0 ( 1 total, Query took 0.0006 sec)
  • 0

#4 Edward

Edward

    Advanced Member

  • Members
  • PipPipPip
  • 1,128 posts

Posted 3 May 2012 - 12:55 PM

The "not equal" operator in SQL is "<>", not "!=".


Ive used != quite a bit in the recent exercises i was doing, possibly this is because mysql could have different functions to other SQL software, there is also mssql for example!
  • 0

#5 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 3 May 2012 - 1:00 PM

The "not equal" operator in SQL is "<>", not "!=".

it is not the operator
I ran the query on mysqlbench and it worked but when i use it in my code, it doesnt work!
  • 0

#6 Edward

Edward

    Advanced Member

  • Members
  • PipPipPip
  • 1,128 posts

Posted 3 May 2012 - 1:00 PM

I just looked this up in Larry's book, okay

!= (also <>) Both mean Not equal to,

There you go Hartley San.
  • 0

#7 markifornia

markifornia

    Advanced Member

  • Members
  • PipPipPip
  • 112 posts
  • LocationSan Diego, California

Posted 3 May 2012 - 4:24 PM

I just looked this up in Larry's book, okay

!= (also <>) Both mean Not equal to,

There you go Hartley San.


I'm just chiming in here learning any way I can. But you can definitely use != operator in SQL statements. Larry's book page 271, I have actually used the method there to check if emails aren't already taken.

Here is the SQL statement:
$q = "SELECT user_id FROM users WHERE email="$e" AND user_id !="$id";

I haven't gone into OOP yet, but perhaps your issue lies here:

//bind param
$stmt->bind_param('is',$ID, $Email);

Have you tried binding them this way?


// Prepare
$stmt = mysqli_prepare($dbc, $sql);

// Bind
mysqli_stmt_bind_param($stmt, 'is', $ID, $Email);

You can start debugging this way.

-M
  • 1

#8 Paul Swanson

Paul Swanson

    Excellent Advisor

  • Members
  • PipPipPip
  • 163 posts
  • LocationPortland, OR, USA

Posted 3 May 2012 - 4:47 PM

The error message is telling you that $stmt isn't an object. Find out if there is an error in the prepare function call by adding this below that function call:
echo $mysqli->error;

Check the PHP Manual for details: mysqli::$error
  • 0

#9 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 3 May 2012 - 8:04 PM

No luck and the odd thing I deployed my files online and I don't get everything working as I do locally.

I have used a prepare statement inside a loop and bind param to the nested prepare statement. the first statement works fine but the one inside the loop it doesn't work though it works locally
  • 0

#10 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,906 posts
  • LocationState College, PA (USA)

Posted 4 May 2012 - 7:35 AM

Yeah, your prepare isn't working, which probably means there's a difference in how the local vs. live databases are set up.
  • 0

#11 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 4 May 2012 - 10:01 AM

Yeah, your prepare isn't working, which probably means there's a difference in how the local vs. live databases are set up.


What do you suggest in this situation ?

I have the same problem with another non prepared query.
The problem is with the query inside the loop.

in the first query I get the question and inside the loop I get the answer for each question using the question id

this query works on my machine but it doesn't work online.

<?php
$sql ="SELECT SurveyID, SurveyName
FROM survey LIMIT 1";
$result = $mysqli->query($sql);
$rec = $result->fetch_array();
$SurveyID = $rec['SurveyID'];
$SurveyName = $rec['SurveyName'];
echo "<h2 class=\"SurveyName\"> $SurveyName</h2>";

$sql ="SELECT QuestionID, Question
FROM question
WHERE SurveyID ='".mysqli_real_escape_string($mysqli, $SurveyID)."'";
$result = $mysqli->query($sql);
$ID = array();
while($rec = $result->fetch_array())
{
$QuestionID = $rec['QuestionID'];
echo "<span class=\"Question\">". $rec['Question']. "</span>";
$ID[] = $rec['QuestionID'];
$_SESSION['Questions'] = $ID;

echo "<input type=\"hidden\" name=\"ID[]\" value=\"{$QuestionID}\"/>";
///Get answers
$sqlAnswer ="SELECT AnswerID, Answer, VoteCount
FROM Answer WHERE QuestionID ='".mysqli_real_escape_string($mysqli, $QuestionID)."'";
$AnswerResult =$mysqli->query($sqlAnswer);

//fetch the result
while($rec = $AnswerResult->fetch_array())
{
$AnswerID = $rec['AnswerID'];
$Answer = $rec['Answer'];
$VoteCount = $rec['VoteCount'];
echo "<input type=\"radio\" name=\"QuestionID[$QuestionID]\" value=\"{$AnswerID}\" id=\"{$QuestionID}\"";
if(isset($_COOKIE[$SurveyID]))
{
echo "disabled";
}
echo "/> $Answer<br />";
}
}
?>
  • 0

#12 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,064 posts
  • LocationOslo, Norway

Posted 4 May 2012 - 10:26 AM

Sure the mysqli extension is available on your live host?
  • 0

#13 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 4 May 2012 - 10:32 AM

Sure the mysqli extension is available on your live host?


Yes it is available.
  • 0

#14 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,906 posts
  • LocationState College, PA (USA)

Posted 4 May 2012 - 10:50 AM

You don't have any error reporting at all. My suggestion would be to add code that:
A) Tests for a positive result before continuing
B ) Reports the MySQL error (during development, not production) should there be a negative result.
  • 0

#15 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 4 May 2012 - 11:16 AM

You don't have any error reporting at all. My suggestion would be to add code that:
A) Tests for a positive result before continuing
B) Reports the MySQL error (during development, not production) should there be a negative result.


Thanks for the tip
the problem was with using capital letter for the name of the table in my query.
on my machine the name of the tables case insensitive and on the production it is case sensitive
  • 0

#16 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 4 May 2012 - 11:30 AM

How do I change the setting so I make it case sensitive on my machine?
  • 0

#17 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,906 posts
  • LocationState College, PA (USA)

Posted 4 May 2012 - 1:59 PM

Offhand, I don't know. I *believe* it's based upon the operating system, because tables are references to files. So if the OS is case-sensitive, the DB is, too.

My best advice with everything is to always act as if everything will be case sensitive.
  • 0

#18 Paul Swanson

Paul Swanson

    Excellent Advisor

  • Members
  • PipPipPip
  • 163 posts
  • LocationPortland, OR, USA

Posted 4 May 2012 - 5:47 PM

I believe the default with newer versions of MySQL is now to only allow lower-case table names. You can change that by adding the following to my.ini:
# added to support upper-case letters in tablenames
lower_case_table_names=0
That solved a problem with not being able to create table names with upper-case letters for me using MySQL 5.0.8 dev client on Windows 7. You will have to stop and restart MySQL for the changes to take effect, of course.

I was going to suggest that your query may be failing because it appears that you are quoting SurveyID and QuestionID, which I would have expected to be integers, not strings.
  • 0

#19 bahaa

bahaa

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 4 May 2012 - 6:44 PM

I believe the default with newer versions of MySQL is now to only allow lower-case table names. You can change that by adding the following to my.ini:

# added to support upper-case letters in tablenames
lower_case_table_names=0
That solved a problem with not being able to create table names with upper-case letters for me using MySQL 5.0.8 dev client on Windows 7. You will have to stop and restart MySQL for the changes to take effect, of course.

I was going to suggest that your query may be failing because it appears that you are quoting SurveyID and QuestionID, which I would have expected to be integers, not strings.


I don't use upper case for the table's name but by mistake in my query I used upper case for my table name in one of the functions.

Everything worked fine after i fixed that problem.
  • 0

#20 Edward

Edward

    Advanced Member

  • Members
  • PipPipPip
  • 1,128 posts

Posted 5 May 2012 - 3:53 AM

We need to make it a rule of the thumb, when something doesn't work on MySQL check the "php.ini" file.
  • 0