Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

SQL select string with where clause

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
runnerjp2005
Asked:
runnerjp2005
  • 16
  • 8
  • 3
1 Solution
 
Dan CraciunIT ConsultantCommented:
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
 
runnerjp2005Author Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
runnerjp2005Author Commented:
whats the best way to show you the database?
0
 
Dan CraciunIT ConsultantCommented:
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
 
runnerjp2005Author Commented:
2 ticks
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
runnerjp2005Author Commented:
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
 
runnerjp2005Author Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
runnerjp2005Author Commented:
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
 
runnerjp2005Author Commented:
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
 
runnerjp2005Author Commented:
Note colourcode it just sets colour to green i over 95% and red if under 80%
0
 
runnerjp2005Author Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
runnerjp2005Author Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
runnerjp2005Author Commented:
^but i need to search all dates- its just the stats withi the team names i need to pull
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
runnerjp2005Author Commented:
so really i want to do it so

"select * from Dokuments where home = ALLTHETEAMNAMESHERE or AWAY = ALLTHETEAMNAMESHERE
0
 
runnerjp2005Author Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
runnerjp2005Author Commented:
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
 
runnerjp2005Author Commented:
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
 
Dan CraciunIT ConsultantCommented:
Use the IN syntax:
WHERE `home` IN ('Valerenga', 'Lillestrom') OR `away` IN ('Valerenga', 'Lillestrom')
0
 
runnerjp2005Author Commented:
Brilliant works great... thanks for you help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now