Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0

    Problem with WHERE clause, at least I think so


    Hi,

    this is a code that is part of my web site:

    Code:
    if(array_key_exists('search', $_POST))
    {
    		
    	$sql = 'SELECT COUNT(article_id) FROM articles WHERE discipline = ? AND author = ?';
    	
    	$stmt = $conn -> stmt_init();
    	if($stmt -> prepare($sql))
    	{
    		$stmt -> bind_param('ss', $_POST['discipline'], $_POST['author']);
    
    	}
    	
    }
    
    else
    {
    	$sql = "SELECT COUNT(article_id) FROM articles";
    }
    
    
    $result = $conn->query($sql) ;
    $row_indexed = $result->fetch_row();(this is my line 49)
    When I load it in browser i get these error:


    Fatal error: Call to a member function fetch_row() on a non-object in line 49


    I am already tired of trying all the possible variations of the code. If someone can help me I'd be glad!

    Just to explain a bit: when first loads page, it loads all of the records from the db. And up until that point everything is ok. But, when I would click my search button with the filtering options, as is in the code for author and discipline, than the fatal error comes in.

    Viktor
  2. #2
  3. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    What you have in the first part of the if totally conflicts with what you try to do afterwards. Rearrange or rewrite the code.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0
    Originally Posted by requinix
    What you have in the first part of the if totally conflicts with what you try to do afterwards. Rearrange or rewrite the code.
    Definitely don't know how to do that. Will you be glad to give some more precise answer???

    Thanx
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0
    OK...

    Maybe I demand to much... But, still, will someone give me a resource or whatever where I can find a php code with mysqli for filtering input from a database. I have seen it working on million websites, and I cannot do it by myself. Depressive... It is simple: I have database that at the loading of the page loads all the records from the database. Than when I have some input in few select tags, I want to filter the input from the database, according to the inputs in the select tags...

    Thanks again...
  8. #5
  9. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    It's not a question of filtering or whatever: you've written code that doesn't work because it tries to do two different things at the same time. You can't do a prepared statement with $stmt and all, not execute it, and then try to run the $sql as just a normal SQL statement.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0
    But I tried and executing the $stmt variable... Nothing changed... I do not understand, where is the ambiguity in the code... If the $_POST array exist, do something, if not do something else ???
  12. #7
  13. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    To get it out of the way, move those final two lines of code into the else block - that's where they work best.

    For mysqli_stmt you prepare (done), bind_param to send data in (done), execute, store_result to buffer the results locally for better performance, bind_result to get data out, fetch to get a row, then keep calling fetch as long as there are results.
    What code did you try?

    Comments on this post

    • viktorjano agrees : requinix rulez!!!
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0
    Code:
    if(array_key_exists('search', $_POST))
    		{
    			$sql = "SELECT COUNT(article_id) AS count_articles FROM articles 	WHERE discipline = ? AND author = ?";	
    			$stmt = $conn -> stmt_init();
    			if($stmt -> prepare($sql))
    								{
    									$stmt -> bind_param('ss', $_POST['discipline'], $_POST['discipline']);
    									$stmt -> execute();
    									$stmt -> store_result();
    									$result = $stmt -> bind_result($count_articles);
    									while($result)
    									{
    										$stmt -> fetch();
    									}
    								}
    		}
    		
    else
    {
    
    	$sql = "SELECT COUNT(article_id) FROM articles";
    	$result = $conn->query($sql) ;
    	$row_indexed = $result->fetch_row();
    	
    }
    
    
    
    $rows = $row_indexed[0];
    Is it good so far?

    By the way, I need to get value for the $rows varible, that gives me the number of articles that match the filters. I am not sure what to do after while loop...
  16. #9
  17. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    Up to the fetching part. You don't actually need a loop yourself because you know there's only one row being returned. Call fetch() once and you're done.

    Except now you have $count_articles set or $row_indexed set, depending on which branch of code executed. Pick one variable name so you don't have to worry about it.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,031
    Rep Power
    377
    why dont you just google a mysql_i example and then go from there?
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0
    Originally Posted by paulh1983
    why dont you just google a mysql_i example and then go from there?
    I tried... I couldn't find anything that would have led me to the solution of the problem...
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0
    Originally Posted by requinix
    Up to the fetching part. You don't actually need a loop yourself because you know there's only one row being returned. Call fetch() once and you're done.

    Except now you have $count_articles set or $row_indexed set, depending on which branch of code executed. Pick one variable name so you don't have to worry about it.

    Code:
    if(array_key_exists('search', $_POST))
    	{
    		$sql = "SELECT COUNT(article_id) AS result FROM articles WHERE discipline = ? AND author = ?";	
    		$stmt = $conn -> stmt_init();
    	if($stmt -> prepare($sql))
    		{
    		$stmt -> bind_param('ss', $_POST['discipline'], $_POST['discipline']);
    		$stmt -> execute();
    	        $stmt -> store_result();
    		$result = $stmt -> bind_result($result);							       
    
    
                    $row_indexed = $stmt -> fetch();
    		}
    	}
    		
    else
    {
    
    	$sql = "SELECT COUNT(article_id) FROM articles";
    	$result = $conn->query($sql) ;
    	$row_indexed = $result->fetch_row();
    	
    }

    Does not return the fatal error... but still not getting the result... Namely, after the code above, I have a variable $rows that gives me feedback for the number of rows fetched from the query. But when the code from the if statement is executed nothing happens.

    i.e.

    $rows = $row_indexed[0];
  24. #13
  25. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    Probably because you changed the part of the code that I said was correct into something that is now incorrect.

    1. Go back to what you had before
    2. Change the variable you used with bind_result to be $row_indexed
    3. Delete the while loop
    4. Call fetch() once.

    And one more thing: modify the code in the else branch so that it leaves the branch with $row_indexed being the number and not an array for the entire row.
    The whole point of most of what I've been saying is to make it so that both halves of the if behave similarly: when either of them exits you have one particular variable with one particular value. $row_indexed being a number. No $count_articles, no arrays.
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0
    The code works.

    Yet it stuck on another place where I make another sql statement on the same page.

    Code:
    if(array_key_exists('search', $_POST))
    		{
    			$sql = 'SELECT article_id, author, discipline, keywords, contents, reference, DATE_FORMAT(created, "%b %y") AS created FROM articles WHERE discipline = ? AND author = ? LIMIT '.($page - 1)  * $page_rows .", " .$page_rows;	
    	$stmt = $conn -> stmt_init();
    	if($stmt -> prepare($sql))
    	{
    	$stmt -> bind_param('ss', $_POST['discipline'], $_POST['author']);
    	$stmt -> execute();
    	$stmt -> store_result();
    	$stmt -> bind_result($article_id, $author, $discipline, $keywords, $contents, $reference, $created);
    	$stmt -> fetch();
    	}
    }
    		
    else
    {
    
    	$sql = 'SELECT article_id, author, discipline, keywords, contents, reference, DATE_FORMAT(created, "%b %y") AS created FROM articles LIMIT ' .($page - 1)  * $page_rows .", " .$page_rows;
    	
    		$result = $conn -> query($sql);
    		$row = $result -> fetch_assoc();
    
    }
    Down in the page I make references to the $row array. But when the if statement is executed, variables are bind as self standing, not in an array... Don't know how to solve this

    OK... buddy, you really helped, this one I have to try hard to solve by my own. Thanks for the effort!!!
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    28
    Rep Power
    0

    Arrow


    May I ask only if this is a possible solution:

    Code:
    if code
    {
    //as it is
    }
    
    
    
    else {  	$sql = 'SELECT article_id, author, discipline, keywords, contents, reference, DATE_FORMAT(created, "%b %y") AS created FROM articles LIMIT ' .($page - 1)  * $page_rows .", " .$page_rows; 
    
    $stmt = $conn -> stmt_init();
    	if($stmt -> prepare($sql))
    	{
    	$stmt -> execute();
    	$stmt -> store_result();
    	$stmt -> bind_result($article_id, $author, $discipline, $keywords, $contents, $reference, $created);
    	$stmt -> fetch();
    	}	 		
    
    
    }

    in other words to work out the else statement same way as if statement. I am sure not if I can do that without binding parameters at all, just executing and binding result.


    Thanks!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo