Search for a string in a sql table with php

[doppis]

Somewhat new with PHP but I think I'm on the right track. I'm trying to search for a serial number on a sql table. I want a response of the serial # and what column its in. There is 4 columns if that helps (id, 30, 32, 31). (serials 002062008002220 002062008002222 002062008002233 are in the table)

When I enter in a serial # 002062008002220 (all serials are 16 digits long). I get a blank page to show up...

 my form

<form action="search.php" method="get">
<input id="serials" type="text" name="serials" />
<input type="submit" />
</form>

my php

<?php

$con=mysqli_connect("localhost", "xxx", "xxx", "nsmserials");

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

$search = mysql_real_escape_string($_REQUEST['serials']);
$query = "SELECT 30, 32, 31 FROM nsmtable WHERE 30 LIKE '%$search%' OR 32 LIKE '%$search%' OR 31 LIKE '%$search%'";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)){
    echo "<li><span class='name'><b>{$row['serials']}</b></span></li>";
}

mysqli_close($con);

?>

 

 

[Jacques1]

Um, what? Did you literally name your columns “30”, “31” and “32”?

 

Those are far from valid idenfifiers. How about choosing sensible names?

Edited June 4, 2014 by Jacques1

[Psycho]

Also, if the records in the 'nsmtable' table have multiple serial numbers, then those numbers should most likely be stores in a separate, associated table.

 

Second, the query you are using is using the LIKE operator, when you state that you are entering the full serial number. If you are expecting the input to be the full ID and don't want/need to allow partial matches, then use the = comparison operator. But, the LIKE operator would work if there was an exact match (based on how you are using it).

 

But, your problem is likely due to the fact that you are only SELECTing the columns 30, 31 & 32 (yeah, those are pretty poor names). But, in your output you are trying to echo the value for a field names 'serials'. Since you didn't select that field in the query that value doesn't exist.

 

EDIT: Although it appears you need to modify a lot (DB structure, field names, logic, etc.) I just wanted to throw this out there. If you do expect the users to enter the full serial number and not partials, you could simplify that query like this

 

SELECT serials, 30, 31, 32
FROM nsmtable
WHERE $search IN (30, 31, 32)

Edited June 4, 2014 by Psycho

[gizmola]

What you've constructed in terms of the database/table structure is what is called a "repeating group".  It is "denormalized" meaning it does not conform to the basic normal forms that a relational database should use.  This is leading to your having to search 3x instead of once.

 

However, things are actually worse than that when you are employing a LIKE '%...%' query.

 

 

WHERE 30 LIKE '%$search%' OR 32 LIKE '%$search%' OR 31 LIKE '%$search%'"

 

When you put a '%' at the front of your search criteria you guarantee that the database can't use an index to help with that search.  Your query will "tablescan" or in other words, it will have to read every single row in the table and look at the contents of every row to determine if a match exists.  This puts stress on your database, and is entirely cache/configuration/IO bound.  

 

As your dataset increases, the performance will steadily decline.

 

This may be acceptable, if you have a small dataset, especially if you have sufficient resources available to the mysql server such that the dataset is likely to be in cache, however, you'll certainly see that on the first such query, it will take whatever time is necessary to read the entire table off disks.  Also, any attempt to index those columns is a complete waste of resources.

 

While this has nothing to do with the issue you have reported, I thought I'd give you a heads up in advance.

 

Last but not least -- the mysql_ functions are being removed in php 5.5.  It's been years since this was announced.  You should not be writing NEW php code using mysql_.  You either should be using mysqli_ or pdo/mysql at this point.

[fastsol]

Actually he/she has both mysql and mysqli in the posted code!

[doppis]

I'm having some trouble figuring out what everyone is suggesting. Is there a technical reason why not to use 30, 32, 31 for column names or?

 

In the future, its actually going to be 30, 32, 31global, 31canada, 31fga, 31international, 31mexico (or something along that lines).

 

Should I make a table for each? There will be a total of 6000ish serials that will be scattered on all of the above columns. I feel like its a waste of tables for the small amount of data.

 

I need an exact match...the user will have to copy/paste the exact serial.

 

I'm sorry if my code looks terrible...I am new

[Psycho]

I'm having some trouble figuring out what everyone is suggesting. Is there a technical reason why not to use 30, 32, 31 for column names or?

 

Technical, no. But, you should always give fields, variables, functions, etc. meaningful names that give you an idea of what the thing/value contains or it's purpose. '30' tells me nothing about what that field is for.

 

 

In the future, its actually going to be 30, 32, 31global, 31canada, 31fga, 31international, 31mexico (or something along that lines).

 

Should I make a table for each? There will be a total of 6000ish serials that will be scattered on all of the above columns. I feel like its a waste of tables for the small amount of data.

 

That's the real problem. You don't just make additional columns when you have repetitive data associated with a record. Based on the above it seems each serial should be associated with a country. So, you would want (at the very least) two tables for this date

 

The 'nsmtable' table should only contain the one-to-one type data associated with those records. It should NOT include data associated with the serials since there can be many associated with each record.

 

Then you need a serials table. That table would include fields for:

serial_id (primary ID for the table)

nsm_id (the primary key of the record from the 'nsmtable' that the serial is associated with, also called a foreign key reference)

serial (the actual serial value)

country

 

The country could, for simplicity, just include a country code (http://en.wikipedia.org/wiki/ISO_3166-1_alpha-3) or name. But, ideally you would have a foreign key reference to the primary key in a separate table of country records.

 

Go do some research on database normalization.

[Barand]

Here's one reason - sql treats numbers as number2

mysql> SELECT * FROM test;
+----+---------------------+-------+-------+
| id | creation            | 30    | 31    |
+----+---------------------+-------+-------+
|  1 | 2013-02-22 17:43:56 | aaaaa | bbbbb |
|  2 | 2013-02-22 17:43:56 | ccccc | bbbbb |
|  4 | 2013-02-22 17:43:56 | ddddd | bbbbb |
|  5 | 2013-02-22 00:00:00 | ddddd | bbbbb |
+----+---------------------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT 30, 31 FROM test;
+----+----+
| 30 | 31 |
+----+----+
| 30 | 31 |
| 30 | 31 |
| 30 | 31 |
| 30 | 31 |
+----+----+
4 rows in set (0.00 sec)

Read the mysql manual regarding identifier names - you were given the link by Jaques2 in reply #2

Edited June 4, 2014 by Barand

[doppis]

Thank you for the input...I'll do a little research on database normalization.

[Jacques1]

Is there a technical reason why not to use 30, 32, 31 for column names or?

 

First and foremost, this is a matter of understandability.

 

The names are supposed to describe the content of the field. A bunch of cryptic numbers doesn't describe anything. What is “30”? The price of 30 apples?

 

This may not seem important to you as long as you're working alone. You surely know what those magic numbers mean. But as soon as other people need to work on this, or if you resume the project after a longer pause, bad naming becomes a serious problem. The table is simply impossible to understand without detailed background knowledge.

 

And, yes, there's also a technical reason: You can't have numbers as identifiers. The only workaround is to wrap the numbers in backticks, but that's really awful. Just use proper names.

 

 

 

In the future, its actually going to be 30, 32, 31global, 31canada, 31fga, 31international, 31mexico (or something along that lines).

 

Should I make a table for each?

 

No, you should make a row for each. That's how data is stored in the relational model: as a row in a table.

 

How exactly the table should be designed depends on your data. I have no idea how that looks like.

 

 

 

I need an exact match...the user will have to copy/paste the exact serial.

 

Then don't use the LIKE operator. The sole purpose of LIKE is to search for partial matches (hence the name).

 

 

 

I'm sorry if my code looks terrible...I am new 

 

I assume you've simply merged two pieces of code you've found somewhere on the Internet?

 

Well, it's better to actually learn PHP and write your own code. Get rid of the old mysql_* functions. Personally, I'd also get rid of MySQLi and use the PDO interface instead. It's much more convenient.

 

See this PDO tutorial. It explains everything you need to know to properly query a database.

[mac_gyver]

additionally, you need to have php's error_reporting set to E_ALL and display_errors set to ON in your php.ini on your development system so that php will help you by reporting and displaying all the errors it detects. you would have been getting a number of error messages due to the mixing of mysql (no i) and mysqli functions that don't work together.

[doppis]

First and foremost, this is a matter of understandability.

 

The names are supposed to describe the content of the field. A bunch of cryptic numbers doesn't describe anything. What is “30”? The price of 30 apples?

 

This may not seem important to you as long as you're working alone. You surely know what those magic numbers mean. But as soon as other people need to work on this, or if you resume the project after a longer pause, bad naming becomes a serious problem. The table is simply impossible to understand without detailed background knowledge.

 

And, yes, there's also a technical reason: You can't have numbers as identifiers. The only workaround is to wrap the numbers in backticks, but that's really awful. Just use proper names.

 

 

No, you should make a row for each. That's how data is stored in the relational model: as a row in a table.

 

How exactly the table should be designed depends on your data. I have no idea how that looks like.

 

 

 

Anyone and everyone at my company will know what these numbers mean but it looks like I should change that for the identifier issue.

 

Example on how I seen this all working out. Currently, 30(30 is a server by the way...as well as 32 and 31) has around 4000 devices or serial numbers. How am I suppose to associate the 4000 serial numbers to 30 if 30 is a row. That was my thinking of making 30 a column and then I would search through the column for a match of the serial number the user submitted in the html form.

 

Maybe this is all over my head and I need to look into what pyscho stated - database normalization (which im still confused about but I'll be reading up on.)

[Jacques1]

How am I suppose to associate the 4000 serial numbers to 30 if 30 is a row.

 

No, you associate a serial number to a server with a row. 

 

You make a table for your servers. And then you make a table to associate serial numbers with servers. So there's a column named “serial_number” and a column named “server” pointing to the server table. If the serial number “FOOBAR” should be associated with server “30”, you insert a row into the association table:

('FOOBAR', 30)

[Barand]

Your normalized tables would look something like this

                                           +-------+---------------------------+
                                           | code  |  country                  |
                                           +-------+---------------------------+
                                           |  1    |  USA                      |
                                           |  2    |  Mexico                   |
                                           |  3    |  Canada                   |
                                           |  4    |  Global                   |
                                           +-------+---------------------------+
                                              |
               +------------------------------+                            
               |
+---------+----------+----------+--------------------+
| nsmid   | country  | server   |  serialno          |
+---------+----------+----------+--------------------+
|  1      |   1      |   30     | 002062008002220    |
|  1      |   3      |   31     | 002062008002222    |
|  2      |   2      |   30     | 002062008002233    |
|  2      |   3      |   31     | 002062008002244    |
+---------+----------+----------+--------------------+

[doppis]

AHHH...that makes a lot more sense now

[Jacques1]

Make a separate table for the servers and have the server column point to that table. Otherwise, this column is unrestricted and may contain any number.

[gizmola]

Also once you use the structure Barand provided AND you remove leading '%' from the LIKE queries, you'll have a performant system for querying for serial numbers with a  single index on the serialno column.  If you want to provide some extra usability you could have your query add variations to it, so:

 

 

/// $searchno contains criteria
 
SELECT * from SERIAL where serialno LIKE '$searchno' OR serialno LIKE '00$searchno';