Connecting to a new Database in PHP

General PHP Help
#1

Greetings,

I am attempting to connect a new database table to one of my PHP websites. The data is modified using Microsoft Access. This data is then linked to the website via PHP. The part of the code that needs to change is as follows:

$conn = odbc_connect(‘database’,‘user’,‘password’);

$sql = "select * from SPX_NFLPlayer,NFLMASTER where NFLMASTER.pyid=SPX_NFLPlayer.PlayerID and SPX_NFLPlayer.PlayerID = " . $PlayerId;

$rs = odbc_exec($conn,$sql);

This is the original code. The databases shown here are no longer used as we moved our data to a new table called FBGRatings2. I have tried to get this new table linked to my PHP code, but it won’t work. Here is what I tried:

$conn = odbc_connect(‘database’,‘user’,‘password’);

$sql = “select * from FBGRatings” . $PlayerId;

$rs = odbc_exec($conn,$sql);

I thought that it had something to do with the $sql line, but I am not sure. Any help would be great considering that I am a novice. Thank you!

Dan

#2

Welcome to our site. Normally when you select data from a database such as your original code you use an
index to get the correct data from the database.

The code: $sql = “select * from FBGRatings” . $PlayerId; is incorrect. It is taking on the player id at the end
of the query which would not work. It was missing the WHERE clause. It should be something like:
$sql = “select * from FBGRatings WHERE PlayerID=” . $PlayerId;

In your version of the sql you have an odd WHERE clause. Not sure, but, try this version:

$sql = “select * from SPX_NFLPlayer, NFLMASTER where NFLMASTER.pyid=” $PlayerI . " and SPX_NFLPlayer.PlayerID = " . $PlayerId;

You can’t reference a where from a select that has not been completed yet. NFLMASTER.pvid=SPX_NFLPlayer.PlayerID=$PlayerId won’t work as it has not actually finished selecting the
second part yet…

Good luck…

#3

Thank you so much for the reply. I tried it your way. Like this:

$sql = “select * from FBGRatings WHERE PlayerID=” . $PlayerId;

Now, when I go to the actual page that would harbor this information, all of the data is blank, so it obviously didn’t connect to the database. What else could I be missing?

Here is the site that this PHP links to:

http://www.fbgratings.com/members/profiletest.php?PlayerID=1

So as the player ID changes, the player information in the database changes. Make sense? Would it help if I put the whole code up?

#4

Well, not really sure what you are asking for. I am assuming that your profiletest.php file is the one I saw
in the link you posted. The code we discussed is just the connection string and query. It creates the one
“RECORDSET” you named $rs. You will have to link that data somewhere in the profiletest page. We did
not see any of that. How is the data from the old link pulled out and used in the display?

Is the new database loaded with data? Perhaps it is empty or at least your table you are accessing.
Table FBGRatings… Did you access it directly using your MS-Access to make sure the data is there?
If the table is empty, you get no results. You can check the number of rows returned to see if you did really
get data from the query. Also, your query did not check for errors to see if it worked or failed. So,
something like this would show you how many rows you got from your query…
$count = odbc_num_rows($rs);
Also, you need to fetch the data to be able to use it before you can display it. Something like:
$arr = odbc_fetch_array($rs); (Fetches an array of the data in $rs…)

Not sure if this helps or makes it more confusing…

#5

The profiletest.php is the one from the link. The data in the Access table is linked to profiletest.php via the rows. The old data is in another table that matches up to the links on the php page the same way that I want to do it here. Basically, the site is the same, but there is a new data source with new column names. I already changed the column names to match the new data table.

The new database table is already loaded with new data. It is not empty. Table FBGRatings has been accessed and is full of data. Where do I use the fetch code? Where do I put it? Would it help if I just put the code up? See the attachment. That is the full profiletest.php but in text form so I can attach it.

Let me know what you think.

ProfileTest.txt (20.7 KB)

#6

Well, Dcebb2001, the code is usable although extremely badly formed. It wastes a lot of the server’s time
and resources duplicating data. But, that is for later. Let’s get it working first. Your data is not showing on
your displayed HTML. So, either it is not connecting to the odbc, the query failed, or the player you selected
is invalid. Your code does not include any kind of error checking so it is hard to know which of these failed.

Normally, you execute a odbc function and then test if there was an error. If there is you throw out an error.
The PHP “die” function kills the page and displays the error. Of course in a productional system, you would
send the errormsg() by email to the webmaster so that they could fix it. In a test system, it does not really
matter. So, after each ODBC function call, use this line to see if any of your commands fail…

if (odbc_error()) die(odbc_errormsg());

Now, the other issue is if all the commands run okay, you still might get no results back if the ID number is
not a valid one. (No data does not mean an error!) To fix that, you need to check the number of items you
returned in the query. Something like this:

Replace this part of your code:
$rs = odbc_exec($conn,$sql);
while(odbc_fetch_row($rs)) {
with this version:
$rs = odbc_exec($conn,$sql);
if (odbc_num_rows($rs)==0) die(“No Records Found for this Player!”);
while(odbc_fetch_row($rs)) {

This will kill the page so you know there is no data for this player. Normally, you would not kill it and show
an error message but, just put up a nice square saying there was no data found for that ID number.

Hope that helps!

#7

OK I changed that part of the code. I got the no records found message. No matter what I change the code to, I get the same message.

HOWEVER…

I can prove that there is data that needs to be accessed. Here is a screenshot of playerID = 1 from the Access Database attached. Note the player ID in the first column and a bunch of data to the right of it. This should be working, right?

What else could be wrong?

Player1.JPG
Player1.JPG1677×322 106 KB

#8

First, I would recommend PDO. If/ when you decide to move away from Access, the amount of code that needs to change is the connection string.

Second, I don’t think your connection string is proper, if it is trying to access a mdb Access file.

#9

The entire set of websites is run through Access tables so we won’t be changing anytime soon. I have to work within the Access tables and the PHP I have set up now. However, if you have suggestions for changing the code to getting this to work, I am all ears.

#10

Well, what I see is that you are using WHERE PlayerId= And, in the pix it is PlayerID …

Fix the spelling…

#11

What do you mean? I don’t see a spelling error. What line do you see it on? What section? What do I need to correct?

#12

Sorry, it is correct. I am getting tired. 1AM here… Bed calling…

So, try this for now… SELECT * FROM FBGRatings and see if it gives you records.
If so, then it is something in the query. If not, then it is the connection.

For checking the connection change:
$conn = odbc_connect(‘tsx2’,‘BrianH’,‘tsxfootball’);
to
$conn = odbc_connect(‘tsx2’,‘BrianH’,‘tsxfootball’);
if (odbc_error()) die(odbc_errormsg());
I am wondering if these are the correct connection strings…

Normally, you would log into your server, log into your sql system and run a query from there to test.
But, with ODBC, it is harder to do. You can go into ACCESS and run a query there. Not sure…

#13

Well, one more possibility before bed… I noticed you are using the REQUEST array for your incoming fields.
Did you actually alter the query just to test like this:
$qry = “SELECT * FROM FBGRatings WHERE PlayerID=1”; not the $_REQUEST’d ones???
If that works, then it is that you are using the REQUEST array instead of the $_POST array. Your code you
posted does not show how the system gets to your PHP code. If it is posted, you need to use $_POST not
$_REQUEST. There is quite a difference…

This post talks about it further: http://www.w3schools.com/tags/ref_httpmethods.asp

#14

Thank you kindly for the replies before nodding off.

I tried the SELECT * FROM FBGRatings and that didn’t work.

Then I tried the stuff you recommended to change on the $conn line. No dice.

Tried to set it to $qry = “SELECT * FROM FBGRatings WHERE PlayerID=1”;. Didn’t work.

Then I changed the 4 REQUEST values to POST. That didn’t work either.

I wonder if I just have to have someone rewrite the entire code based upon our database parameters.

#15

You must not be making a connection to the database. When you did these:
$conn = odbc_connect(‘tsx2’,‘BrianH’,‘tsxfootball’);
if (odbc_error()) die(odbc_errormsg());

did it give you an error message?

#16

Is the database stored on the server? Is it on your own system? ODBC on a PC will have a control panel
where you can see your errors and connection strings. If it is on a Microsoft data server, the server can give
you the ODBC connection info. ODBC is such an odd duck these days. Most systems use MySQLi or an MS
product.

Well, I will check in on you in the morning and look at this again. Nite!

#17

When I replaced that line $conn…I got the “No Records Found For This Player!” error that you had me build into the PHP file earlier.

The database is on a server. I basically log into Access then can pull up any of the tables in Access to edit them. I did have to create a .dsn file in my PC’s “My Documents” folder, though. Not sure if that has anything to do with it.

Thanks again for all the help!

#18

Okay, so the .dsn file is an ODBC thingy that some systems need. This means basically that your Access is
going thru the computer’s library of ODBC connections and then to your server. All normal.

Now, I suggest just make a one page small test page that has only this one problem on it. Below is one
that might work well for you. Basically, it just contains the PHP code to connect to the DB, pull on line of
data and display that. Nothing more. I scraped this from your posted text file… Copy this to a new file
and store on the server. Then, go to that page online and see what it gives you on the screen… I added
in full error reporting and a header title line so that you should at least get that line!

filename: dbtest.php
[php]

Database Test...

Testing DB Connection

<?PHP // Turn on all PHP error messages... error_reporting(E_ALL); ini_set('display_errors', 1); // Connect to database... $conn = odbc_connect('tsx2','BrianH','tsxfootball'); if (odbc_error()) { echo "Could not connect to database!
"; die(odbc_errormsg()); } // Execute a selection of data... $sql = "SELECT * FROM FBGRatings WHERE PlayerID=1"; $rs = odbc_exec($conn, $sql); if (odbc_error()) { echo "SELECT from database failed!
"; die(odbc_errormsg()); } // Display all of the data retrieved... odbc_result_all($rs, "border=1"); ?> [/php] This is not tested as I do not have a ODBC database set up. I added comments and error messages. Note that the last command is like doing a WHILE clause and displays all of the data in a table. Try this and post the results...
#19

Alrighty!

I created the .php file and copied in the text you wrote. I uploaded that to the server and went to the link. Here is what I got below. This is the correct player and the correct data. PLUS, it has the correct columns and values that the new dbo table has! Awesome! Now, it’s just a blank page, but it is getting me my results. I think there are some formatting issues such as the numbers having one decimal place instead of just being whole numbers, but maybe we can fix that later. The point is that it appears that it is linking to the new table!!! What do we do now???

EDIT: Oh, and here is the link! http://www.fbgratings.com/members/dbtest.php

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Testing DB Connection PlayerID OldPlayerID RatingsLastUpdated PlayerPic DraftRound DraftPick DraftOverallPick DraftTeam Change GeneralPOS Status Number Team LastName FirstName College Position 2ndPosition RatingNotes Height Weight Born YearsPro DraftYear OVR STR AGI SPD ACC AWR CTH CAR THP KPW KAC TAK PBK RBK JMP RET TRK ELU BCV SFA SPM JKM IBL RBS RBF PBS PBF PMV FMV BSH PUR PRC MCV ZCV SPC CIT RTE POW PRS REL SAC MAC DAC RUN PAC INJ STA TGH CONS CONF Hometown Handedness LastPlayed SkinTone Face ChestSize BellySize ArmSize ArmDefinition RearSize ThighSize CalfSize FeetSize HelmetType FaceMask Visor FaceMarks NeckPad PadHeight PadWidth Sleeves BackPlate FlakJacket ArmSleeves LeftElbow RightElbow LeftWrist RightWrist LeftHand RightHand HandWarmer Towel ThighPads KneePads LeftAnkle RightAnkle SockHeight Shoes QBStyle StanceStyle QBStyleTrait ThrowBallAway SensePressure ForcesPasses AggressiveCatches RACCatches FightsForExtraYards CoversTheBall MakesSidelineCatches DropsOpenPasses LBStyle PlaysBallInTheAir BigHitter StripsBall UtilizesSwimMove UtilizesSpinMove Clutch HighMotor Penalty Contract YearsLeft TotalSalary CapSalary SigningBonus 1.0 76413.0 2015-08-14 00:00:00.000 76413nfl NULL NULL NULL Undrafted NULL DT NULL 0.0 31.0 Aaitui Isaako Nevada-Las Vegas DT NULL NULL 6'4" 307.0 1/25/1987 2.0 2011.0 56.0 57.0 52.0 55.0 53.0 58.0 21.0 36.0 17.0 20.0 15.0 85.0 32.0 35.0 34.0 11.0 28.0 29.0 21.0 31.0 29.0 27.0 28.0 28.0 29.0 23.0 26.0 83.0 54.0 66.0 76.0 59.0 17.0 21.0 17.0 13.0 12.0 73.0 19.0 19.0 5.0 5.0 5.0 5.0 5.0 78.0 63.0 77.0 48.0 48.0 Pago Pago, American Samoa NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

#20

Great! We made some headway! Well, next, you have to compare your code in the live page to this
code to see where you have made a mistake in the live version.

Obviously, there is nothing wrong with the server or the database or getting to it. (Accessing it.)

It means either you are not getting the player ID correctly and therefore the code is not asking the
correct query. In your old code you have:

$sql = "select * from FBGRatings WHERE PlayerID=" . $PlayerID;
(Note that it says $PlayerId in the text file, so that might be the problem!)

I think you should also display the $PlayerID, but, we can actually display the query itself and then copy
it to the test file we just got to work. So, change it to something like this:

$sql = "select * from FBGRatings WHERE PlayerID=" . $PlayerID;
    echo "<br><br>QUERY: " . $sql . "<br><br>";

This will show you the actual query on screen so you can see where it is failing. Did that make sense to
you? Hope so… I think we are very close to solving this one!

Oh, also, the test page only “dumped” the data in the row, it was not formatted or whatever… That is done
inside the live page and will CSS mostly… Another project, LOL…

Sponsor our Newsletter | Privacy Policy | Terms of Service