query help
I've got a text field called source_recid. It stores half string half number like strings in it. Example shop.orders.32442 the syntax is DATABASENAME.TABLENAME.RECID My goal is to scan this col and find out the biggest RECID ( the integer) in it. So, in a case like this shop.orders.32442 shop.orders.82000 shop.orders.34442 It would be the record whose source_recid is shop.orders.82000. Why? Cause 82000 happens to be the largest integer. What SQL statement would get me that record? One option to this is to create a new column ( the_ids ) and move all the integers in it and then run something like this select source_recid from mytable where source_recid like 'shop.orders.%' order by the_ids DESC LIMIT 1 Is there a way to pull this off without going thru this step?
distinct count operation with the use of where count $num
Hi, I'm trying to get this work; SELECT distinct `term`,count(*) as count FROM blp_sql_distinct_temp_table where count = 5 group by `term` order by count DESC But I get this error; Unknown column 'count' in 'where clause' How do I get only those records whose group by count is above 5? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
mixing and matching mysql mssql whileloop within an if case
Please take a look at the following code and tell me if there is a way
around it.
if ($current_server_is_mysql):
while ($row = mysql_fetch_assoc($RS)) {
else:
while( $row = sqlsrv_fetch_array( $RS, SQLSRV_FETCH_ASSOC)){
endif:
Depending on the server I'm working with, I'd like to compile my
records into the $RS recordset the proper/native way. If I can get
pass this part, the rest should be all right cause both mysql and
mssql $row can be tapped into the same way. For example,
$row['fieldname'] will give me the field value whether the row was a
mysql or mssql resource. So it all boils down to the above snippet
failing.
The error I get points to the else: part in the above snippet.
Parse error: syntax error, unexpected T_ELSE in
D:\Hosting\5291100\html\blueprint\pages\populate_migration_table.php
on line 415
I can understand why I am getting this error.
But, I'm hoping you guys can offer a work-around it without me
resorting to duplicate the entire while loop she-bang.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.
Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument parse it out to come up
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}
The magic would be happening in the $fields argument.
The example in my original question was like this.
I'm repeating it for convenience purposes.
quote
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs
peopleID___stateID
1__1
2__4
3__5
people table is like this;
___peopleID_FName_
___1joe
___2bob___
___3charlie_
and finally the state table goes like this;
___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__
What's the most straightforward way to achieve the following view with one
SQL statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
/quote
if the final table ( PeopleAndStates ) view I want were to be as follows;
peopleID__stateID_
1_1___
2_4___
3_5___
Then I would have called the function like this;
showtable($dbh,$myTable,peopleID,stateID)
But if I want to get, the following view instead;
peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___
I would like to be able to call my function as follows;
showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID
FName),stateID)
To mean the following;
When you are outputting the peopleID, provide the corresponding Fname
field from the PeopleTable where peopleID there is equal to the peopleID
you are outputting.
What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID
FName), I can extract the pieces and place it in the final SQL.
I'm not sure if you all get the idea of how such a function make debugging
super easy.
Once you write the parser, you can deploy it over many different cases such
as
ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on.
So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than
SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates
2012/4/12 Halász Sándor h...@tbbs.net
2012/04/11 17:51 -0500, Peter Brawley
select b.peopleID, concat('(',p.fname,,')'), b.stateID,
concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
Since the names are the same in the tables, it works to use USING, too,
and you are relieved of the burden of an alias:
from bridge
join people USING(peopleID)
join state USING(stateID)
If the fields peopleId and stateID are the only field names in common,
NATURAL JOIN also works.
from bridge
NATURAL join people
NATURAL join state
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
a sql injection attempt
My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a sql injection attempt
sorry, i overlooked that this IS a mysql mailing-list and we are running ms-sql in this particular case. good catch... I'd appreciate any insight though. On Mon, Feb 13, 2012 at 1:56 PM, Gary Smith shady...@l33t-d00d.co.uk wrote: On 13/02/2012 21:48, Haluk Karamete wrote: My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? Nothing on MySQL - however, if the back end was an MS SQL server then the first query would prove that the user had access to the sysobjects table (ie wasn't constrained within a view, etc). The second is - the char(124) evaluates to |user|=0. I'm not sure what this one does, tbh. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a sql injection attempt
Gary, you've mentioned that the user would have had access to the sysobjects Let's assume he did. The page that this attempt occurred is hard-wired to display a single record in detail view. In the code, I have a bunch of echo $row-title kind of statements... I'm even more curious now; what kind of goodies this evil user would have gotten with having access to the sysobjects from the query string? I mean how would my page display sysobjects data when I don't have anything to do with echo sysobjects stuff? can you shed some light maybe? thx. On Mon, Feb 13, 2012 at 1:56 PM, Gary Smith shady...@l33t-d00d.co.uk wrote: On 13/02/2012 21:48, Haluk Karamete wrote: My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? Nothing on MySQL - however, if the back end was an MS SQL server then the first query would prove that the user had access to the sysobjects table (ie wasn't constrained within a view, etc). The second is - the char(124) evaluates to |user|=0. I'm not sure what this one does, tbh. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
case insensitivity
How do I do case insensitive searches and replace operations? Is there an easy way to do this? Like some sort of a server level setting telling mySQL to ignore case for once and for all? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: case insensitivity
Thank you for your reply... But isn't like very very slow in comparison to a none-like straight search? Isn't it an overkill for a case sensitivity issue? It appears to me that like has its own usage arena and case sensitivity issue won't just justify the use of it... MSSQL can be configured to work in either mode. Isn't such a thing for mySQL? For most of the time, I would not care about case-sensitivity. So I won't mind configuring the entire mysql operation to be case insensitive once and for all? On another note, what harms do you see with that if any? On Mon, Jan 16, 2012 at 7:41 PM, Willy Mularto sangpr...@gmail.com wrote: use LIKE On Jan 17, 2012, at 10:36 AM, Haluk Karamete wrote: How do I do case insensitive searches and replace operations? Is there an easy way to do this? Like some sort of a server level setting telling mySQL to ignore case for once and for all? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
