Page 1 of 1

MySQL for a Noob

Posted: Tue Sep 04, 2007 6:17 pm
by dsevcik
Hello, I run the site http://www.mathcelebrity.com as well as a baseball site that will go live at the end of the year. The question I have will cover both sites. I'm fairly new to PHP/MySQL and after a few weeks, I was able to query the database based on my search item hard coated into my search, and not from a search box return value. I can attach the code here, but I was wondering about the following which I'm unable to accomplish:

I have a database called baseball. The table name is batting. The table looks like this

Name PlayerID Average OBP SLG
Mike Clark clarkm01 .250 .333 .350
Paul Jones jonesp01 .285 .350 .412
Brandon Jones jonesb01 .302 .352 .385
Jim Farnsworth farnsj01 .320 .425 .350
Rick Peters petersr01 .360 .375 .420


I need a double search done in php to connect to this MySQL table. The first search takes an input box where a person would enter a name or part of a name, say Jones. I want them to find anything like Jones. I believe the code is %Jones%. That input will run a query against the above table to return any name like Jones. The trick here is I want it to return the Jones entries in a row by row format, as a link, which displays the full name, but is really an identity for the PlayerID. This first query results return on a page called namesearch.php. Now, when I click that identity link mentioned before, I want a 2nd query ran against the same database which is now searching by PlayerID, but returns the 3 columns, Average, OBP, and SLG in a html table on another page called battingresult.php.

Let me know if you want me to attach my code. Thank you for your help!

Posted: Wed Sep 05, 2007 9:10 am
by dsevcik
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


For further clarification, here is the code below.  If I replace the $_POST["user"] with the actual user id, it works.  Any help would be appreciated.  User is the name of the form submission from the page before it.

Code: Select all

<html>
<head>
  <title>Baseball Stats</title>
</head>
<body>
<?php
  

// Show the STATS in an HTML <table>
  function display($result)
  {

     echo "<h1>Ray Knight Regular Season Batting Stats</h1>\n";

     // Start a table, with column headers
       echo "\n<table border = '1' width='35%'>\n<tr>\n" .
          "\n\t<th>Player ID</th>" .
          "\n\t<th>Year</th>" .
          "\n\t<th>Stint</th>" .
          "\n\t<th>Team</th>" .
          "\n\t<th>League</th>" .
	  "\n\t<th>G</th>" .
	  "\n\t<th>AB</th>" .
	  "\n\t<th>R</th>" .
	  "\n\t<th>H</th>" .
	  "\n\t<th>2B</th>" .
	  "\n\t<th>3B</th>" .
  	  "\n\t<th>HR</th>" .
	  "\n\t<th>RBI</th>" .
	  "\n\t<th>SB</th>" .
	  "\n\t<th>CS</th>" .
	  "\n\t<th>BB</th>" .
	  "\n\t<th>SO</th>" .
	  "\n\t<th>IBB</th>" .
	  "\n\t<th>HBP</th>" .
	  "\n\t<th>SH</th>" .
	  "\n\t<th>SF</th>" .
          "\n\t<th>GIDP</th>" .
          "\n</tr>";

     // Until there are no rows in the result set,
     // fetch a row into the $row array and ...
     while ($row = @ mysql_fetch_row($result))
     {
        // ... start a TABLE row ...
        echo "\n<tr>";

        // ... and print out each of the attributes
        // in that row as a separate TD (Table Data).
        foreach($row as $data)
           echo "\n\t<td><p align='center'> $data </td>";

        // Finish the row
        echo "\n</tr>";
     }

     // Then, finish the table
     echo "\n</table>\n";
  }

  // Get regular season batting results
  $query = "SELECT * FROM batting where playerid = $_POST["user"]";


  // Connect to the MySQL server
  if (!($connection = @ mysql_connect(localhost,
                                      root,
                                      root)))
     die("Cannot connect");

  if (!(mysql_select_db("baseball", $connection)))
     showerror(  );

  // Run the query on the connection
  if (!($result = @ mysql_query ($query, $connection)))
     showerror(  );


  // Display the results
  display($result);


  // Close the connection
  if (!(mysql_close($connection)))
     showerror(  );
?>
</body>
</html>

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Sep 05, 2007 1:50 pm
by califdon
OK, you're off to a good start. You have some quotation mark issues on the line

Code: Select all

$query = "SELECT * FROM batting where playerid = $_POST["user"]";
Change to single quotes around user.

Since your query is a SELECT *, it will return rows with several columns. Your code is not exploding the rows to separate the fields. I prefer to use mysql_fetch_assoc() (although mysql_fetch_row() essentially does the same thing), then use extract($row) to assign values to variables that have the same names as the fields, like this:

Code: Select all

$row=mysql_fetch_assoc($result);
    extract($row);
Then you can use variables like this:

Code: Select all

echo "<td>$Name</td> <td>$Average</td> <td>$OBP</td> <td>$SPG</td>";
I want it to return the Jones entries in a row by row format, as a link, which displays the full name, but is really an identity for the PlayerID. This first query results return on a page called namesearch.php. Now, when I click that identity link mentioned before, I want a 2nd query ran against the same database which is now searching by PlayerID, but returns the 3 columns, Average, OBP, and SLG in a html table on another page called battingresult.php
Right. In another script, you just need to have another loop pretty much like the one you already made for this one, only instead of just echoing the <td> and contents, you have to format an anchor link, something like this:

Code: Select all

echo "\n\t<td><p align='center'><a href='battingresult.php?ID=$PlayerID'>$Name</a> </td>";
The script battingresult.php must look for a $_GET parameter named ID and execute a query using that value in the WHERE clause, then return the data for display.

Oh, a final and important issue: when you use a $_POST (or $_GET) variable directly in a SQL statement, you are exposing your script to malicious so-called SQL injection, which can compromise your database and even other files on your web site. You should always assign the $_POST variable to some variable name, then check that variable to insure that its contents are valid. There are numerous threads on this security issue on this and other forums. Search for "SQL injection" here and on Google for details.

Posted: Wed Sep 05, 2007 2:16 pm
by dsevcik
Thank you for the assistance, I will try this tonight and report back!

Posted: Wed Sep 05, 2007 3:06 pm
by califdon
califdon wrote:

Code: Select all

echo "\n\t<td><p align='center'><a href='battingresult.php?ID=$PlayerID'>$Name</a> </td>";
I'm afraid I gave you some bad code there! It should be:

Code: Select all

echo "\n\t<td><p align='center'><a href='battingresult.php?ID=".$PlayerID."'>$Name</a> </td>";