MySQL

[Cedars]

OK I am going nuts.

 

I have a table I am trying to Query via phpMyAdmin. I am very new to using this stuff but I have had success with creating searchable access DBs, creating forms to search multiple inputs and produce results that are accurate.

 

This table has 8 fields including the ID (auto-increment)

 

Its a bird table with Type, Name, Nester, Sp, Su, F, W

 

What I am trying to do is query the Type for Hawk.

 

I keep getting zero results when I know there are 13 records

 

Heres the query:

 

SELECT * FROM `cmbird2` WHERE `Type` = 'Hawk'

And another try:

SELECT * FROM cmbird2 WHERE Type= 'Hawk'

 

Heres another version of the query that doesnt produce errors, but doesnt find the results

 

SELECT `cmbird2`.*

FROM cmbird2

WHERE (`cmbird2`.`Type`= 'Hawk');

 

Now when I do a wildcard search on the whole database %Hawk% via phpmyadmin, rather than just the one the table, it produces the results I am seeking.

 

But using this search produces no results:

SELECT * FROM cmbird2 WHERE 'Type'= '%Hawk%'

Same with this one:

SELECT * FROM cmbird2 WHERE Type = 'Hawk'

And this:

SELECT * FROM cmbird2 WHERE Type = ('Hawk')

 

What am I doing wrong?

[Tormod]

SELECT `cmbird2`.*
FROM cmbird2
WHERE (`cmbird2`.`Type`= 'Hawk');

 

I don't understand what you're trying to do with this code - it selects the database name rather than a column. You want to just SELECT * if you want all columns.

 

You wouldn't put quotes around the table names, only the words you want to match. (As an aside I'd also be careful about naming a column "Type" since it could easily have been a reserved word (although it isn't in MySQL)).

 

Try this:

 

SELECT * FROM cmbird2 WHERE (Type = 'Hawk');

[alexander]

first of all, are you sure that the table cmbird2 contains a Hawk in the field Type?

 

second most common SQL mistake i find is LetTeR capItALiZatiOn, mysql table names and column and row names are case sensitive, so if you do "describe table <table name>" should give you that the the names of rows and columns in the table...

 

you could then try

 

SELECT * FROM cmbird2 WHERE Type like '%Hawk%'

[Cedars]

 

I don't understand what you're trying to do with this code - it selects the database name rather than a column. You want to just SELECT * if you want all columns.

 

You wouldn't put quotes around the table names, only the words you want to match. (As an aside I'd also be careful about naming a column "Type" since it could easily have been a reserved word (although it isn't in MySQL)).

 

Try this:

 

SELECT * FROM cmbird2 WHERE (Type = 'Hawk');

 

The above still returned zero results, but I know there are 13 Types whos value is Hawk in this table.

 

What I am trying to do is find the records where the Field is Type and the value = Hawk.

 

I am really new to mySQL so I am quite prone to error for sure. The ultimate goal is to allow visitors to my website to search for (as example) the Types of Hawks found in Crex Meadows and output the resulting table for them, rather than having static pages built for all the types of birds in the Meadows. I already have this info in access for personal use.

 

I guess I should be happy I managed to get it out of access and into mySQL on the server (its a start). I have looked thru most of the data for goofy characters, took out some white spaces, etc after the upload.

 

It just drives me nuts cuz I can take the same data, put it into access and get the correct results, but I cant use access on my website. And of course I am looking for freebie diy ways to do this.

[Cedars]

first of all, are you sure that the table cmbird2 contains a Hawk in the field Type?

 

 

you could then try

SELECT * FROM cmbird2 WHERE Type like '%Hawk%' 

 

That WORKED!! I was missing the Type like '%Hawk%' in my query.

I was using = (and a billion combos of Hawk).

 

Gawd...its always something dumb.

 

Thank you!

[Buffy]

I actually once got to personally ask Chris Date why he and Ted Codd chose "like" and "%" instead of "=" and "*". I wish I could repeat it, but he mesmerizingly convinced me that the latter symbols are "semantically misleading" and that the weird SQL syntax is "more correct."

 

To this day my code still translates "*" into "%" and dynamically chooses "like" and "=" as appropriate to speed up execution... Ppppbbbbt. :hihi:

 

And don't get me started on single versus double quotes....

 

Another one for the "You know you are a geek when" thread....

 

Where Buffy Like '%rolling stone',

Buffy

[Tormod]

Ah...of course. LIKE and not '=' was something I struggled with a lot when we moved Hypography from an MS SQL database to MySQL. I should have remembered that... :hihi:

[alexander]

rofl Buffy, i'm with you on the single vs double quotes debate... i could never understood why it could not have been made into a single convention, so you never have to think, "wait, or is it a single quote??...."

 

That's what we are here for, Cedars :evil:

[Cedars]

That's what we are here for, Cedars :rolleyes:

 

I went out and bought a book last night. I am gonna read that then bug you guys for help. I was spending too much time looking at scripts without enough clues.

 

900+ flipping pages... eee gads.

[alexander]

please, computer manuals are supposed to be long, i have probably a half dozen c++ books, over 1000 pages each, and thats only c++ books, i have 3 shelves full of computer books and manuals...

[Cedars]

OK this is the third try. The code tag is giving me fits.

 

I have a search form that uses checkboxes. There are no text fields.

_POST

foreach

SELECT

 

There is no INSERT within any of the php search page.

 

Do I have to worry about injection?

if so

What is an easy code to put in?

[C1ay]

What are you trying to do? Search records? Insert records? What form are you referring to?

[alexander]

cedars, you are going to need to be speciffic of what you ask. you are using a form, ok, that uses boxes, ok, to search, ok, but it's not in how you implement the form, its how you write your php, make a new thread, post your form, and php that interacts with it, obviously replace usernames, passwords and paths, but we would need to see code to tell you if someone could potentially use sql injection...

 

on another note on MySQL, if you guys weren't aware of it, Sun, a couple of weeks ago, purchaised MySQL. Strange thing is not why they did it, it's how much they paid. MySQL is worth 75mil at most, but sun put up a bill for it, so something strange going on? what do you guys think?

 

(ps, i feel this is a good thing to discuss in a MySQL thread :eek2: )

[Buffy]

You mainly have to worry about injection when you have a text field that the form, but you have to be aware that injection can occur with *any* statement, not just INSERTs: its mainly an issue of the entry being able to terminate what you have in your code and getting the system to execute something completely different.

 

The main code snippet you're going to work with is to look for single quotes and make sure they don't allow a string to get executed...

 

name = replace(name, "'","''") :eek2:

Buffy

[Cedars]

C1ay and Alex,

 

I did try to post code for this, but the code tag was giving me fits.

 

You mainly have to worry about injection when you have a text field that the form, but you have to be aware that injection can occur with *any* statement, not just INSERTs: its mainly an issue of the entry being able to terminate what you have in your code and getting the system to execute something completely different.

 

The main code snippet you're going to work with is to look for single quotes and make sure they don't allow a string to get executed...

 

name = replace(name, "'","''") :eek2:

Buffy

 

So if I am using an html form which creates an array via Name=MyList[ ] value="something" there is a vulnerability via value? or even potentially MyList[] if someone could bounce their own form to my script?

 

Can I use RewriteCond and add php|PHP to prevent this kind of bouncing in htaccess?

 

Then just to be sure I would need something in the script itself. Heres the basics where I think the issue would arise.

 

if (isset(_POST['check_mark']))

foreach (_POST['check_mark'] as MyCheck)

query = "SELECT * FROM MyTable WHERE (`Type` LIKE 'MyCheck') ORDER BY Family,Genus,Name ASC"
result = mysql_query(query) or die ()
num=mysql_numrows(result)

Being really new to php and mysql, I was pretty proud of myself for creating the script that I did (with alot of forum searching).

 

The rest of the script is just outputting the data into tables so I dont think thats an issue. Any comments on injection issues?

 

**NOTE** I think it was the copy pasted $ that messed up my code tags

[alexander]

Buffy, actually htmlspecialchars() and htmlspecialchars_decode() are your extra special friends in the fight agains sql injection :eek2: and i can not stress enough the point of data checking, check your data, double check it before doing anything with the database...

[alexander]

you can use the [noparse]

[/noparse] tags for php code, makes the code prettier :eek2:

ok, going to go through code line by line and see what is up:

if (isset(_POST['check_mark']));
{
 foreach (_POST['check_mark'] as MyCheck)
 {
   //ok right here, you seem to be missing a piece of code 
   //that checks for the validity of the data, i can write a 
   //form that would pwn your website
   //also prior to inserting any data into your database, your 
   //friend htmlspecialchars() function comes in handy, most of 
   //your dangerous characters, like ' ", etc are all replaced 
   //with escape codes where they will cause no damage in a sql query
   //when retrieving data, you need to only call on 
   //htmlspecialchars_decode() and they turn back into ' ", etc
   query = "SELECT * FROM MyTable 
WHERE (`Type` LIKE 'MyCheck') 
ORDER BY Family,Genus,Name ASC";
   //also using that variable just wasted space till the end 
   //of code execution, you would be more efficient if the 
   //query resided inside the query function
   result = mysql_query(query) or die ();
   //lastly it does not to hurt to check the mysql output for 
   //which error was returned, and log it :)
   num=mysql_numrows(result);
 }
}