Solved

SQL select string with where clause

Posted on 2014-03-05
27
267 Views
Last Modified: 2014-03-05
Hi ALL,

Currently i am selecting everything from my database and sticking it in a datatable and searching through it later on.

The issue is my dataable is so large that when i come to search through it multiple times my page times out!

I have had a think about this and i only actully need to pull from my database teams that are playing that day so i though about doing this:

This code loops though a datatable containing todays football matches and pulls the teams playing (not set a dim ? as yet as im not sure how i would do this)
    For Each getteams As DataRowView In DvTheData2
            getteams.Row.Item("hometeam").Trim()
            getteams.Row.Item("awayteam").Trim()
        Next

Open in new window


Them i run the script to pull everything from the database (Footballclass.getdatatableis just to pull the data)
     
  Dim dtRecords As DataTable = footballclass.GetDataTable("SELECT * FROM soccerstatsgoals")

Open in new window


So what i would like to do is use the first code to pull all the teams (home and away) then run it though a query like so:

SELECT * FROM soccerstatsgoals Where Home = 'hometeamname1' or Away = 'hometeamname1' or Home = 'awayteamname1' or away = 'awayteamname1' or Home = 'hometeamname2' or Away = 'hometeamname2' or Home = 'awayteamname2' or away = 'awayteamname2' .......

There could be 50 odds teams playing that day ...

what would be the best way to go about this~?
0
Comment
Question by:runnerjp2005
  • 16
  • 8
  • 3
27 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Seems you have SQL problems. Paste the CREATE TABLE statement here so we can optimize the query.

"SELECT *" is usually a sign that things will go very wrong very quickly.

HTH,
Dan
0
 

Author Comment

by:runnerjp2005
Comment Utility
Public Shared Function GetDataTable(ByVal sql As String) As DataTable
        Dim rt As New DataTable()
        Dim ds As New DataSet()
        Dim da As New MySqlDataAdapter()
        Dim con As New MySqlConnection(footballclass.GetConnection())
        Dim cmd As New MySqlCommand(sql, con)
        da.SelectCommand = cmd
        da.Fill(ds)
        Try
            rt = ds.Tables(0)
        Catch
            rt = Nothing
        End Try
        Return rt
    End Function
End Class

Open in new window

0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
That is the connector.

We need to see the structure of the database. The tables and their fields, along with the types and indexes.
0
 

Author Comment

by:runnerjp2005
Comment Utility
whats the best way to show you the database?
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Looks like you're only using one table, so please post the result of the following query:

DESCRIBE soccerstatsgoals

It should give you all the field names and types in the table.
0
 

Author Comment

by:runnerjp2005
Comment Utility
2 ticks
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
I assume you have the date the matches are played on in the table.
Where (tblDate = srchDate)
replace tblDate with the date stored in the table and srchDate with the date you wish to search on.  (No need to use team names in this case)
0
 

Author Comment

by:runnerjp2005
Comment Utility
CREATE TABLE `footballdb` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Hometeam` text,
  `Awayteam` text,
  `HTG` text,
  `ATG` text,
  `HalfHTG` text,
  `HalfATG` text,
  `Hred` text,
  `Ared` text,
  `Date` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=12131 DEFAULT CHARSET=latin1;
0
 

Author Comment

by:runnerjp2005
Comment Utility
They only pick todays games....

So i have a list of todays games- I then search my database to pull all the results (number of goals) all teams have scored this season
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
OK, good.
And your goal is to select what data?
No need for sql lingo, just use your words to describe what data you want returned from that table.
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Something like this:

SELECT SUM(`HTG`) as home_goals, SUM(`ATG`) as away_goals
    FROM footballdb
    WHERE `Hometeam`  = "a team"

Open in new window

will give you all the goals scored and received by the team "a team" while playing at home.
0
 

Author Comment

by:runnerjp2005
Comment Utility
OK so what im doing atm is loading all the games into a data-table (i have been told making multiple connections to the mysqldatabase can slow it down and only 1 connection needs to be made.

After that I get the matches on today and pull the teams from todays games and check within the datatable holding a list of this seasons matched and pull all the scores for that team

 Public Shared Function Getthestats(ByVal thedataview As DataView, ByVal hometeam As String, ByVal awayteam As String) As List(Of Integer)
        '' dataview-> dtRecords2
        'need to loop through the results in datview and get all results for it 
        Dim hometeamscoresingle As New List(Of Integer)
        For Each dv2 As DataRowView In thedataview
            If Not dv2.Row.Item("Score") Is DBNull.Value Then
                Dim ATG As String = dv2.Row.Item("Score").split("-")(0)
                Dim HTG As String = dv2.Row.Item("Score").split("-")(1)
                If dv2.Row.Item("Home") = hometeam And Not HTG Is DBNull.Value And Regex.IsMatch(HTG, "^[0-9 ]+$") Then
                    hometeamscoresingle.Add(HTG)
                ElseIf dv2.Row.Item("Away") = hometeam And Not ATG Is DBNull.Value And Regex.IsMatch(ATG, "^[0-9 ]+$") Then
                    hometeamscoresingle.Add(ATG)
                End If
            End If
        Next
        Return hometeamscoresingle
    End Function

Open in new window


So for example if Man U where playing today i would check my database (now loaded in my datatable) for any games they have playing in as the home or away team and pull their score
0
 

Author Comment

by:runnerjp2005
Comment Utility
Also dan i don't want the sum as i need to get the % with this code

setthepercentage(homefulltimestats, "hft")
Function setthepercentage(ByVal thegoals As List(Of Integer), ByVal thename As String) As String
        Dim BTTS As Long = 0
        Dim over05 As Long = 0
        Dim over15 As Long = 0
        Dim over25 As Long = 0
        Dim countelements As Long = 0
        'work out if it is 0.5 - 1,3,4,5,3,2,4,5,
        For Each element As String In thegoals
            'chekc if over 0.5
            countelements = countelements + 1
            'check if its BTTS
            If thename = "BTTS" Then
                If element = 1 Then
                    BTTS = BTTS + 1
                End If
            Else
                If element > 0 Then
                    over05 = over05 + 1
                    'check if over 1.5
                End If
                If element > 1 Then
                    over15 = over15 + 1
                    'check if over 2.5
                End If
                If element > 2 Then
                    over25 = over25 + 1
                End If
            End If

        Next

        ' over05 = (over05 / countelements) * 100

        Session(thename & "over05") = colourcodeit(Math.Round((over05 / countelements) * 100), over05, countelements)

        ' over15 = (over15 / countelements) * 100


        Session(thename & "over15") = colourcodeit(Math.Round((over15 / countelements) * 100), over15, countelements)


        'over25 = (over25 / countelements) * 100


        Session(thename & "over25") = colourcodeit(Math.Round((over25 / countelements) * 100), over25, countelements)

        Session(thename) = colourcodeit(Math.Round((BTTS / countelements) * 100), BTTS, countelements)
    End Function

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:runnerjp2005
Comment Utility
Note colourcode it just sets colour to green i over 95% and red if under 80%
0
 

Author Comment

by:runnerjp2005
Comment Utility
Like a tit i posted the wrong database!!!

Below is the correct one sorry :(

CREATE TABLE `soccerstatsgoals` (
  `Date` varchar(255) DEFAULT NULL,
  `League` varchar(255) DEFAULT NULL,
  `Home` varchar(255) DEFAULT NULL,
  `Away` varchar(255) DEFAULT NULL,
  `Score` varchar(255) DEFAULT NULL,
  `Result` varchar(255) DEFAULT NULL,
  `HT Result` varchar(255) DEFAULT NULL,
  `HT Res` varchar(255) DEFAULT NULL,
  `Avg H` varchar(255) DEFAULT NULL,
  `Max H` varchar(255) DEFAULT NULL,
  `Avg D` varchar(255) DEFAULT NULL,
  `Max D` varchar(255) DEFAULT NULL,
  `Avg A` varchar(255) DEFAULT NULL,
  `Max A` varchar(255) DEFAULT NULL,
  `Avg Ov 2.5` varchar(255) DEFAULT NULL,
  `Max Ov 2.5` varchar(255) DEFAULT NULL,
  `Avg Un 2.5` varchar(255) DEFAULT NULL,
  `Max Un 2.5` varchar(255) DEFAULT NULL,
  `Season` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window


score gets the score so 2-2, 0-1 ect and i have to break it down when i have pulled it from the database by splitting it
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
As the table grows, selecting everything from it will take longer and longer.

I suggest you rethink your logic so you extract from the database only what you need. Most of what you want can be done in SQL.
0
 

Author Comment

by:runnerjp2005
Comment Utility
Thats why i was looking at only pulling the data that contain the teams name... but i have to somehow loop through all the teams names that day
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
I guess you didn't see my earlier comment, here it is in full:

I assume you have the date the matches are played on in the table.
Where (tblDate = srchDate)
replace tblDate with the date stored in the table and srchDate with the date you wish to search on.  (No need to use team names in this case)
0
 

Author Comment

by:runnerjp2005
Comment Utility
^but i need to search all dates- its just the stats withi the team names i need to pull
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
This was in the original question:
I have had a think about this and i only actully need to pull from my database teams that are playing that day so i though about doing this:

hence my comment telling you how to get all matches played on a given day
0
 

Author Comment

by:runnerjp2005
Comment Utility
so really i want to do it so

"select * from Dokuments where home = ALLTHETEAMNAMESHERE or AWAY = ALLTHETEAMNAMESHERE
0
 

Author Comment

by:runnerjp2005
Comment Utility
Yes so i only want to pull teams from my database that are playing that day...

So today the teams that are playing are Chelsea - man u - stoke - hull city  <- these are the only teams i need to pull stats in within my database
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
OK, so assuming you have a separate table where the today's matches are kept:

SELECT field1, field2, field3, ..., field4 FROM yourTable 
    WHERE name IN (
        SELECT home from scheduleTable WHERE date='2014-03-02')
    OR name IN (
        SELECT away from scheduleTable WHERE date='2014-03-02')

Open in new window

This will get you get the names for the day's teams.
Never select more than you need. Don't use "*"
0
 

Author Comment

by:runnerjp2005
Comment Utility
OK so i actually grab the teams of "todays" matches from a website and stroe them in a datatabe

so i would be more looking to do this

For Each getteams As DataRowView In DvTheData2
           SELECT field1, field2, field3 FROM Table 
    WHERE Home = 'getteams.Row.Item("hometeam").Trim()' or Away = 'getteams.Row.Item("awayteam").Trim()'
        Next

Open in new window


^ this would loop through each team name and select it from database... would this be the way to do it though
0
 

Author Comment

by:runnerjp2005
Comment Utility
Thought i got it with this

        Dim thefootballsqlstring As String
        Dim thecount As Integer = 0
        For Each getteams As DataRowView In DvTheData2
            If thecount <> 0 Then
                thefootballsqlstring = "or"
            Else
                thefootballsqlstring = ""
            End If
            thefootballsqlstring = thefootballsqlstring & "Home = '" & getteams.Row.Item("hometeam").Trim() & "' or Away = '" & getteams.Row.Item("hometeam").Trim() & "' or Home = '" & getteams.Row.Item("Awayteam").Trim() & "' or Away = '" & getteams.Row.Item("Awayteam").Trim() & "'"
            thecount = thecount + 1
        Next
        Dim dtRecords As DataTable = footballclass.GetDataTable("SELECT * FROM soccerstatsgoals WHERE" & thefootballsqlstring)

Open in new window


but i get error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Valerenga' or Away = 'Valerenga' or Home = 'Lillestrom' or Away = 'Lillestrom' at line 1
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
Comment Utility
Use the IN syntax:
WHERE `home` IN ('Valerenga', 'Lillestrom') OR `away` IN ('Valerenga', 'Lillestrom')
0
 

Author Closing Comment

by:runnerjp2005
Comment Utility
Brilliant works great... thanks for you help :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now