[kf6yvd]

I am working on a simple script to search through some data I have stored in a Database. This will only be used by me to make it easier to find data. SQL is a fairly new thing for me so kinda learning as I go here.

Here is the query command I am using:

$sql = mysql_query("select * from tablenamehere where Serial like '%$term%' or DName like '%$term%' ");

The problem I am experiencing is when I add a second query command it will only return result from the bottom one and it completely ignores the one above it.

I am sure it is something simple I have overlooked and I just can't figure out what I am missing...

[Atli]

What exactly do you mean by "when I add a second query command"? Could you show us an example of that?

[kf6yvd]

Keeping in mind that i really don't know sql I am probably doing this wrong but the code would look like this

$sql = mysql_query("select * from table1 where Serial like '%$term%' or DName like '%$term%' ");
$sql = mysql_query("select * from table2 where Serial like '%$term%' or DName like '%$term%' ");

It would only read the data from Table 2 and ignore Table 1. I am willing to bet I am just coding it wrong...

This post has been edited by kf6yvd: 19 August 2012 - 09:53 AM

[Atli]

You are simply overwriting the $sql variable with your new results.

Consider this:

$word = "First";
$word = "Second";

echo $word; 
// Prints: "Second"

Do you see the problem?

[kf6yvd]

I figured it was something like that. I will toy around with the code you posted and figure out how to put it all together. I can assure you I will be back seeking more advice

[kf6yvd]

Just started working on this but is there a limit to the number of tables it can utilize before the queries need to be achieved a different way?

[kf6yvd]

Ok this does not seem to be working no matter what I do so maybe this will help..

I have one massive table at this moment it searches just fine. I need to break this down into multiple tables. I need to be able to add data to a certain table and have it update the 'ID' (I need the new data to appear at a certain point in the search results and I was using the 'ID' to determine its location) and the simplest way I can picture to do this is multiple tables with each city having its own table. I can dive into that later though to make that process simpler as I want to get searching multiple tables in a single database functioning first.

This is then accessed through a simple search page and the results are displayed in a table format. There are 10 columns in every table (all identical to each other). 'ID' is set to AI but I want the searchable results based on two certain columns (Serial and DName).

This is probably a very simple task but is beyond my knowledge. Since what I was trying to do does not seem to be working I am hoping that a description of what I am trying to achieve will enable someone to educate me on how to do this.

This post has been edited by kf6yvd: 19 August 2012 - 01:07 PM

[Atli]

The problem I was trying to show you earlier doesn't really have anything to do with the SQL. It's just a basic error in your programming logic. Every time you do $var = "something"; you are assigning the value on the right side of the = to the variable on the left side. If there is something already being stored in the variable, it is replaced by the new value.

So when you do:

$result = mysql_query("SELECT ... FROM tbla");
$result = mysql_query("SELECT ... FROM tblb");

The first line saves the result set from "tbla" into the $result variable. And that's all good, and works fine. However, as soon as the second line is executed, the result set from "tblb" is stored in $result, replacing the result set from "tbla" that was previously stored there. - Both queries executed fine, no doubt, but you are discarding the first set before you read it.

So the solution is either to use the result set from the first query before executing the second query, or to execute them like that but store the result value in different variables.

kf6yvd, on 19 August 2012 - 08:06 PM, said:

I have one massive table at this moment it searches just fine. I need to break this down into multiple tables.

Why do you need to do that?

kf6yvd, on 19 August 2012 - 08:06 PM, said:

.... and the simplest way I can picture to do this is multiple tables with each city having its own table.

I'd advice against that. It goes against the very basics of a how relational database are meant to be used. The table layout should never have to be altered to accommodation data inserts. - If you find yourself having to create tables when adding data (like, say, when adding a city) then there is a major flaw in the design.

In a relational database you use relationships to link rows from one table to another table. For example, if you need to store a list of street names in a city, you create one table to store cities and another to store streets. Then you add a column in the street table that references the city table, linking the street to whichever city it belongs to. Like:

+------+     +---------+
| city |     | street  |
+------+     +---------+
| id   |>-|  | id      |
| name |  |->| city_id |
+------+     | name    |
             +---------+

[kf6yvd]

When I am trying to add a row into the table I need it to appear in a certain location in the search results. They are currently organized numerically utilizing the ID. Everytime I add a row it adds the row at the end using the auto increment. When I try to determine what ID it will use when adding the new row it gives me a error that it already exists.

Perhaps a simpler solution would be trying to figure out how to get the 'ID' to update itself and increase all entries in database by one when I add a row with a selected ID number instead of the auto increment option

This post has been edited by kf6yvd: 19 August 2012 - 01:40 PM

[kf6yvd]

Since most of this seems a bit beyond my knowledge I though that perhaps using the same table with Custom Ids instead of Numerical would be a better approach.

I still need them to maintain a certain order even after inserting a row. So using the IDs of ABQS1-01, ABQS1-02, ABQS1-03, and ABQS1-04 should appear in that order was my thought.

I inserted rows with -01, -02 and -03. I then added a row with ABQS2-01 and then ABQS1-04 and -o4 did not drop in where I thought it should in the database or the results I got back. I Toyed around with adding a sort order command and came up with this

Line 5 $term = $_POST['term'];
Line 6
Line 7 $sql = mysql_query("SELECT  * apparatusdatabase ORDER BY 'ID' where Serial like '%$term%' or DName like '%$term%' ");

Lines 1-3 are the ones to log into database, 4, 5 and 8 are blank. Line 9 started the echo commands. I recieved a boolean error on line 9 (means its above line 9 if I am not mistaken).

Again its probably something incredibly simple I am missing but any thoughts?

[JackOfAllTrades]

You're not listening to the sage advice of Atli, simply ignoring the latter part of Atli's post #8 which went into why your plan is not the right thing to do. What you are attempting to do here is just wrong on its face. You're going to be hard-pressed to find anyone to assist you when you're violating basic database principles.

[kf6yvd]

I was not ignoring it I was trying to piece it all together and understand the functions to make it all work. I figured out how to make it work though. I managed to make it work with one database utilizing a SortOrder column to get the output order I wanted.

As far as this working it was just a execution order it seems

$sql = mysql_query("SELECT  * apparatusdatabase ORDER BY 'ID' where Serial like '%$term%' or DName like '%$term%' ");

The where command being executed before ORDER BY SortOrder worked out just fine for me

[Atli]

There is one thing in that query - a part from the ORDER BY being positioned before the WHERE - I didn't notice before, that would mess up the positioning.

You've got the "ID" quoted, like: ORDER BY 'ID'. This means that MySQL won't order the rows based on the "ID" column, but rather that it will order them based on the string "ID". Meaning that every row in the set will have an equal value for the ORDER BY, making it return the rows in no particular order. (Which usually means the same order they went it, but that's not a guarantee.)

It should either be just: ORDER BY ID, or seeing as "ID" is a reserved keyword, more like: ORDER BY `ID`. - Note the difference in the quote marks there. Those aren't single-quotes, like you use on strings, but back-ticks: `.

Back-ticks are what MySQL uses to enclose complex identifiers. They are not interchangeable with the single-quotes it uses around string values.

[kf6yvd]

Yeah I ended up using this (dumping the ID completely)

$sql = mysql_query("select * from apparatusdatabase where Serial like '%$term%' or DName like '%$term%' ORDER BY SortOrder");

Created a new column in table called SortOrder and excluded it from search results to get the order I wanted

This post has been edited by kf6yvd: 22 August 2012 - 05:47 PM