Solved

SQL select string with where clause

Posted on 2014-03-05
27
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 8
  • 3
27 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39906039
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
ID: 39906049
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 35

Expert Comment

by:Dan Craciun
ID: 39906056
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:runnerjp2005
ID: 39906058
whats the best way to show you the database?
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39906070
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
ID: 39906072
2 ticks
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39906075
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
ID: 39906077
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
ID: 39906078
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 35

Expert Comment

by:Dan Craciun
ID: 39906081
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 35

Expert Comment

by:Dan Craciun
ID: 39906093
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
ID: 39906099
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
ID: 39906106
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
 

Author Comment

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

Author Comment

by:runnerjp2005
ID: 39906117
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 35

Expert Comment

by:Dan Craciun
ID: 39906176
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
ID: 39906190
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
ID: 39906198
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
ID: 39906207
^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
ID: 39906216
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
ID: 39906223
so really i want to do it so

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

Author Comment

by:runnerjp2005
ID: 39906226
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 35

Expert Comment

by:Dan Craciun
ID: 39906612
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
ID: 39906642
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
ID: 39906802
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 35

Accepted Solution

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

Author Closing Comment

by:runnerjp2005
ID: 39906884
Brilliant works great... thanks for you help :)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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