Weird search problem

[Sparticuz]

Hey all, I've got a grid with a seach that isn't finding my record

For brevity's sake, here's the partial record.

|| *id* || *business_name* || *business_address_line_1* || *business_address_line_2* || *business_address_zip_code*
|| 3380 || Bayer Properties (Cresent Building) || Highland Ave. South || || 35205 ||


I have tried searching for this record in my search part by id, business name, address, everything, and for some reason it never shows up. I'm at a loss. Does anyone have any ideas on how I would go about troubleshooting this?

searching for 3381 works fine.

[TheSmitchell]

Re: Weird search problem

SQL? DBF? Is the record in your grid to start?

[Sparticuz]

Re: Weird search problem

MySQL backend, the record is clearly in the database. (The partial record above was actually an export from HeidiSQL). Grid is hidden to start. Every other search seems to go through just fine, but for some reason that one isn't being displayed.

[TheSmitchell]

Re: Weird search problem

The record is in the database, but is it in your query results for the grid?

I had some issues with MySQL queries where I was filtering on fields for x is not equal to "some value" and it would return everything except those records that were "some value" or NULL. Had to change my logic a bit to include or x is null.

[agillbb]

Re: Weird search problem

Or maybe set the fields to allow empty strings and not allow nulls.

[TheSmitchell]

Re: Weird search problem

Or maybe set the fields to allow empty strings and not allow nulls.

Also a valid solution. The exciting things you find when you port from one database type to another.

[Sparticuz]

Re: Weird search problem

This is not the issue. I search for 3379 it works, I search for 3380 it does not work. It is something in the actual record that is preventing it from being displayed. I changed the key to 3381 and the record still does not show up. I wanted to see if there was a way I could look at the debug for a search. It seems like somehow it's escaping out somewhere and I can't find it. I'm not at my dev box right now, but when I get to it, I'll see if I can post a full sql insert of the row.

[agillbb]

Re: Weird search problem

try writing the query from inside the db environment and see what happens.

[TheSmitchell]

Re: Weird search problem

try writing the query from inside the db environment and see what happens.

One trick I've used is to set the filter such that it should return the problem record. You could add "ID is equal to 3380" in your SQL Query builder's filter. Then test the query. If you get zero results, then there's something going wrong with your original query. (Assuming that you're not just pulling all records from a single table.)

[frankbicknell]

Re: Weird search problem

The problem has to be that the query is excluding the record with either a 'where' or 'having' clause, or maybe select distinct.

[Sparticuz]

Re: Weird search problem

OK, I put the sql in the db and the row didn't come up. Its the sql, I was using a 'Where table1.primarykey = table2.foreignkey' instead of a join and it looks like this row didn't have a corresponding row in table 2, thus preventing the row from being displayed. The reason I wasn't using a join in the first places was because of performance.

SELECT gen_customers.*, gen_customer_contacts.*, 'a' AS Fran_name, 'b' AS last_pp, 'c' AS last_inspection, 'd' AS last_invoice, 'e' AS citystate, 'f' AS citystate2 
FROM gen_customers gen_customers
	 LEFT OUTER JOIN gen_customer_contacts gen_customer_contacts
		 ON  gen_customers.id = gen_customer_contacts.customer_fk

This works now, but look at the performance of the statement "/* 0 rows affected, 7,508 rows found. Duration for 1 query: 0.203 sec. (+ 49.640 sec. network) */" and that is on localhost. Something is screwy...

[agillbb]

Re: Weird search problem

0.2 sec is o.k., but what are you saying about the 50 sec? Is that horrific 50 sec lag across your network?

[Sparticuz]

Re: Weird search problem

It's local. The database is on my computer.
ScreenShot023.png

[agillbb]

Re: Weird search problem

How is that possible.
Try using the A5 Database Explorer to view the data returned from the DB - and see if the lag is reduced.
Check the data returned to make sure it looks right.
You could try getting rid of the .* in your query - only choose the rows you need.

[Sparticuz]

Re: Weird search problem

A5 is the same way.

I did fix the problem. I don't know why this would have mattered but it fixed it. My key on gen_customer_contacts was a compound primary key consisting of auto increment id, and customer_fk. I just dropped it to be a normal primary key with an index on customer_fk and now it's speeding along at .15 seconds.

Thanks for the help :)