topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 4:20 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Sql Syntax Error  (Read 54987 times)

The Code Queryer

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 33
    • View Profile
    • Donate to Member
Sql Syntax Error
« on: April 06, 2019, 12:57 PM »
Folks,

What is wrong with my php mysql query ? Trying to get the script to search my database and show results in pagination format. Using mysqli and procedural styling here.
$total_pages = $conn->query("SELECT * FROM browsing_histories WHERE username = ?")->num_rows;
What is wrong with my php mysql query ?

I get this error:
Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1 in C:\xampp\htdocs\test\search.php:31 Stack trace: #0 C:\xampp\htdocs\test\search.php(31): mysqli->query('SELECT * FROM b...') #1 {main} thrown in C:\xampp\htdocs\test\search.php on line 31

If I remove the "WHERE username = ?" part and make it like the following then I get no error.

$total_pages = $conn->query("SELECT * FROM browsing_histories WHERE username = ?")->num_rows;

But making it like this means getting it to pull all rows from the table. I do not want that. I want to pull matching rows in the "username" column.
How to fix this ? How should it have been ?

Shades

  • Member
  • Joined in 2006
  • **
  • Posts: 2,922
    • View Profile
    • Donate to Member
Re: Sql Syntax Error
« Reply #1 on: April 06, 2019, 05:21 PM »
Here is the link to the SQL syntax for the SELECT command in MariaDB. That is the database software your error code says you are using.

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]


INTO DUMPFILE 'file_name' INTO var_name [, var_name] ]

      [[FOR UPDATE | LOCK IN SHARE MODE] [WAIT n | NOWAIT] ] ]


export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

By your own admission, you stated that you are not a programmer and that you starting to learn. No problem, in that case you should first check if your query works in a database client before you try to put it into PHP scripting. A database client usually has much more helpful ways to describe what is going wrong with your SQL query. on the MariaDB site, the use of HeidiSQL is recommended.

Your query:
SELECT * FROM browsing_histories WHERE username = ?

Doesn't seem it is properly terminated.
And is the question mark allowed as parameter content?
Or is the query treated as if you are looking for one or more user names that have the name '?' ?

Anyway, a database client could already have told you that. More extensive database clients often come with an option to see how long a query takes on the database you are connected to, which tables/indexes are consulted for that query, etc. Might not be useful information for you right now, but if your thing picks up and needs to scale, you will be very glad you have access to such tools.

Another tip: looking for optimizations to make your code faster? If there is any access to any type of database involved, check first how you can optimize your queries first. That is more often than not where you can gain a lot more. Fast code or slow code, both wait just as long for your database query to produce results...

Clipboard01.pngSql Syntax Error

Without access to your database, I just started up my XAMPP environment (which is useful for PHP script development on Windows) to connect to a MySQL database (PHPmyadmin) and show you how the query should be.

I cannot state enough the importance of using a database client first for checking database queries that need to be in code. Which in and of itself is already a sub-optimal practice (for applications that need to scale), but PHP, so who cares.
« Last Edit: April 06, 2019, 05:43 PM by Shades »

The Code Queryer

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 33
    • View Profile
    • Donate to Member
Re: Sql Syntax Error
« Reply #2 on: April 08, 2019, 01:52 PM »
Here is the link to the SQL syntax for the SELECT command in MariaDB. That is the database software your error code says you are using.

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]


INTO DUMPFILE 'file_name' INTO var_name [, var_name] ]

      [[FOR UPDATE | LOCK IN SHARE MODE] [WAIT n | NOWAIT] ] ]


export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

By your own admission, you stated that you are not a programmer and that you starting to learn. No problem, in that case you should first check if your query works in a database client before you try to put it into PHP scripting. A database client usually has much more helpful ways to describe what is going wrong with your SQL query. on the MariaDB site, the use of HeidiSQL is recommended.

Your query:
SELECT * FROM browsing_histories WHERE username = ?

Doesn't seem it is properly terminated.
And is the question mark allowed as parameter content?
Or is the query treated as if you are looking for one or more user names that have the name '?' ?

Anyway, a database client could already have told you that. More extensive database clients often come with an option to see how long a query takes on the database you are connected to, which tables/indexes are consulted for that query, etc. Might not be useful information for you right now, but if your thing picks up and needs to scale, you will be very glad you have access to such tools.

Another tip: looking for optimizations to make your code faster? If there is any access to any type of database involved, check first how you can optimize your queries first. That is more often than not where you can gain a lot more. Fast code or slow code, both wait just as long for your database query to produce results...

[attachthumb=#,msg428965][/attachthumb]

Without access to your database, I just started up my XAMPP environment (which is useful for PHP script development on Windows) to connect to a MySQL database (PHPmyadmin) and show you how the query should be.

I cannot state enough the importance of using a database client first for checking database queries that need to be in code. Which in and of itself is already a sub-optimal practice (for applications that need to scale), but PHP, so who cares.

Ok. I did as you suggested. tested my query in PhpMyAdmin in my xampp. Get error:
"Error
Static analysis:

1 errors were found during analysis.

Unexpected character. (near "?" at position 50)
SQL query: Documentation

SELECT * FROM browsing_histories WHERE username = ? LIMIT 0, 25

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? LIMIT 0, 25' at line 1"

Anyway, you use "?" as placeholders when coding prepared statements and that is why the "?" came-along.

Q1. Anyway, how-about showing me how the query should have been in php ?
Q2. How-about showing us a sample pagination code where username searches can be done ?

So I can learn from your sample and others can learn too ? :)
I been searching on google etc. for weeks now and can't find a tutorial that teaches how to build a pagination script with search feature (eg. username searches) using procedural style and mysqli. I do not know pdo or oop style yet.
And so, your code sample would most likely be the only sample available online. Definitely, will get google indexing your post!

Shades

  • Member
  • Joined in 2006
  • **
  • Posts: 2,922
    • View Profile
    • Donate to Member
Re: Sql Syntax Error
« Reply #3 on: April 12, 2019, 11:04 AM »
15 years or so ago I took a look at working with PHP. Bought books (with example CD included) and such for that journey. Perhaps some 4 or 5 months in, I started to dislike it profoundly and never looked back. Sold all I had about PHP and never did anything real with it anymore. I am sure code quality has improved a lot from the mess it was back then, but meh. You have already more experience with current PHP syntax than I do, so don't expect examples from my end.

So, invest in a digital or analog book about the subject and brush up your knowledge about how to do things right, before you have coded a semi-functional something that is rife with bad coding practices and/or security flaws. Or check videos if that is more of your thing than books. Free online courses from respectable universities are available to you as well. The main point here is to educate yourself first, before going down the rabbit hole (the 'failing to prepare == preparing to fail' thing).

Bumping your head on the material is (and must be) part of the learning process. Using pre-chewed examples won't make you understand the material better. And if you use those examples verbatim in your code, be prepared for lots of misery later on in your project. And by then fixing the technical debt will be a major headache. And you'll likely start over from scratch, doing it right. Except you have lost about two to three times the amount of time in the best of cases.

The placeholder "?" was used because it was mentioned in your example. ? is one of a set of special characters, which need more "treatment" in queries (encapsulation and/or escape characters). And you will have "fun" with applying the type of encapsulation that works well on one operating system and not the other. Code that works on a Windows development box is not guaranteed to work on a Linux production box. Or even a Windows production box (if the development box differs too much from the production box).

In any case, that kind of experience you must learn first hand, else it haunts you your whole career or even a lifetime.

Another tip:
If Google doesn't give you examples of a pagination script with search feature....
...why not search for examples of pagination scripts, other examples of search scripts and combine these yourself on your development box? Losing lots of time looking for one special item or dividing the search into parts and perhaps lose a lot of time making the parts whole. Guess which approach will get you more educated and improve your skills...

DC member Wraith is right about you searching for examples or advice on sites like StackOverflow, SuperUser or even W3Schools. Such sites were created with people like yourself (in the beginnings of their programming career) in mind. To give such persons a helping hand, I mean.