MYSQL Query Help!

Query always returns first record when page loads?

[aboesenecker]

Hi,

I'm teaching myself how to use MYSQL, and having a problem with a query I'm writing. The query works like I'd expect, but when the page first loads, the first record in the recordset is always displayed. Is it something to do with my usage of LIKE?

I'd really appreciate any help. I'm sure it's an easy fix, but in my search for an answer, I haven't been able to turn anything up. All help is appreciated!

Thanks,

Andy

Here's the query:

SELECT sermon_resources.sermon_id, sermon_resources.keywords, sermon_resources.readings_header, sermon_resources.readings, sermon_resources.`year`, sermon_resources.week, sermon_resources.week_text, sermon_resources.short_week_text, sermon_resources.week_pic, sermon_resources.reading_1, sermon_resources.reading_1_text, sermon_resources.reading_1_pic, sermon_resources.reading_2, sermon_resources.reading_2_text, sermon_resources.reading_2_pic, sermon_resources.reading_3, sermon_resources.reading_3_text, sermon_resources.reading_3_pic
FROM sermon_resources
WHERE year = colname OR readings LIKE %colname2% OR keywords LIKE %colname3%
ORDER BY sermon_resources.week

colname =

[aboesenecker]

Please disregard the colname @ the end of message...thanks!

[penders]

The query works like I'd expect, but when the page first loads, the first record in the recordset is always displayed.

What are you expecting to happen?

Are you referring to 'recordset' as being the result of your query (which is what I'd assume), or your entire table data?

[aboesenecker]

Hi,

Thanks for taking the time to help. Sorry if my verbage is confusing...let's see if this will help...

It seems that the page automatically loads the first record in the table. If I use the form and search again, it will/will not display results as expected. The query seems to be working, but the page automatically displays the first record in the table upon opening.

I'm wondering why the first record in the table is automatically displayed when the page loads?

Thanks for your time!

Andy

[penders]

WHERE year = colname OR readings LIKE %colname2% OR keywords LIKE %colname3%

Are you initialising colname, colname2 and colname3 to something appropriate in the beginning ie. when your page first loads? If you are relying just on the values being passed back from your form then this could be the problem as they might be empty or undefined?

[aboesenecker]

I guess I am just relying on the form, as I haven't done anything other than defining colname, colname2, colname3 in the query window via Dreamweaver...

Can you point me in the right direction as far as how to initialize these variables? Is there an article on this, or some code that I can adapt to fit my needs?

I appreciate your help!

Andy

[penders]

I think someone who is perhaps more familiar with Dreamweaver would be better commenting further on this. Are you using PHP to build your query? Is Dreamweaver generating script for you?

If using PHP, then to initialise variables you would do something like this at the top of your script in a PHP block:

$colname = 'something'; 
$colname2 = 'something else'; 
$colname3 = 'another something';

But this really depends on what your actual PHP variables are called and naturally what you want to default them to.

What do you want to get shown when the page first loads? ie. What should those default values be? May be nothing should display at first - just the form?

Sorry, not sure if that helps you that much?!

[aboesenecker]

It does help...I think I'm getting closer...

I am using PHP, although I manually generated the query I originally posted. Not sure what dreamweaver does behind the scenes to make this all happen, though...

Ideally, the form would be displayed, but no search results would be showing. Just the empty "results" fields...

Thanks!

Andy

[penders]

Ideally, the form would be displayed, but no search results would be showing. Just the empty "results" fields...

Ok, so when the page first appears (ie. when your form has NOT been submitted) then don't try to run your SQL to get any results.

If the name of one of your form elements is "colname", this could take the form...

if (isset($_POST['colname'])) { 
 // (1) Read the values from your form 
 // (2) Validate the user input 
 // (3) Build your SQL and execute 
 // (4) Get the results from your query and generate $HTML 
 // (You must be doing 1..4 in some way already in order to get your results output) 
} else { 
 $HTML = 'NO RESULTS YET'; 
} 
echo $HTML; 
// Display your form