View Full Version : PHP Retrieve data from DB Table
igotregistered
11-16-2012, 05:54 PM
I'd like to retrieve player results from a table in my vbulletin database table called _vbullthread. The results would ultimately print to the players own page.
I'm using this script (below) to retrieve the info but the script breaks after I try to run it. There is only one field in the table which has the info the script needs. It is the "Title" field. All values in said field within the database are entered like this....... Bob Smith vs John Doe May 2, 2001 Sports team vs Sports team
<html>
<head>
<title>Player Info</title>
</head>
<body>
<?php
//connect to the database
mysql_connect ("10.x.x.x","username","pw") or die ('Cannot connect to MySQL: ' . mysql_error());
mysql_select_db ("database_name") or die ('Cannot connect to the database: ' . mysql_error());
//query
$query = mysql_query("select name_Bob, name_smith from _vbullthread") or die ('Query is invalid: ' . mysql_error());
//write the results
while ($row = mysql_fetch_array($query)) {
echo $row['name_first'] . " " . $row['name_last'] . "
";
// close the loop
}
?>
</body>
</html>
This script isn't working. I keep getting this message:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/x/o/v/owner/html/bob_smith.php(47) : eval()'d code on line 21
There is no eval()'d code in the code you posted.
Beyond that, there are several issues that you would do well to address:
# If at all possible, you should avoid using the mysql_* functions. #
ext/mysql is outdated and scheduled for deprecation.
It is no longer recommended for new projects, and existing code should be updated to avoid performance and security problems.
Using ext/mysqli (http://php.net/mysqli) or the PDO class (http://php.net/pdo) is recommended.
Read more about choosing an API (http://php.net/mysqlinfo.api.choosing) on php.net.
.
I don't know if that's your real username/password or not, but if it is, you should obfuscate it.
.
In your question, you say you want to select the Title column from the database.
In your code, you attempt to select the name_Bob and name_smith columns.
Subsequently, you attempt to use the name_first and name_last columns (which, obviously, would not exist) from your query result.
Could you please clarify, specifically, what you want to accomplish? Make sure you post the code that is relevant to your problem.
igotregistered
11-17-2012, 01:00 PM
Hi Traq, I sent you my website address in PM.
What I want to do is....
When I add specific players video's to my video portal. I would like to make custom pages with the players statistics, (outside of the video portal) and have only their video's automatically retrieve from the same table the videos live on.
I would like the data parsed from the table to be retrieved by player name.
Thank you for your help.
Could you please post the relevant part of bob_smith.php that uses the eval() function?
As far as the DB goes, does this 'player_stats' table exist already? Or do you need to create it? What is the structure of the video table?
Hi Traq, I sent you my website address in PM.
Is there some reason you don't want the link posted here? You might benefit from other users being able to see it, and offer advice.
igotregistered
11-17-2012, 03:53 PM
ok, it's nhlfightclub.com/hockeyfights
It's setup like a youtube clone. The table the video data is in, is "_vbullthread". There's only one field which has the data. It's the "title" field.
I'm not sure what you mean by what part of bob_smith do I want to use? I'm sorry I'm not familiar with writing scripts, I'm only familiar with designing sites.
I want to build several pages, each dedicated to 1 specific player. As fights are added to the video portal through this page nhlfightclub.com/vbtube_add.php?do=add&pt=0
I would like the specific player I add video's for to have their videos automatically populate on their custom profile page I make. Like this one
nhlfightclub.com/dave_brown.php#dave1
I hope that's more descript, I'm sorry if I wasn't providing enough info. I truly appreciate the help.
I'm not sure what you mean by what part of bob_smith do I want to use? I'm sorry I'm not familiar with writing scripts, I'm only familiar with designing sites.I was referring to your script where the error was encountered:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/x/o/v/owner/html/bob_smith.php(47) : eval()'d code on line 21
As far as what you want to build, I understand your goal. What we would need to know is how everything is currently arranged, and what info we need to have in order to accomplish your goal.
For example, if all of the info is stored together in one field in the database, there is very little hope for being able to use/search it. It's simply not useful stored like this:
table
| Title |
+--------------------------------------------------------------+
| Bob Smith vs John Doe May 2, 2001 Sports team vs Sports team |
It would need to be stored in an organized way, more like:
table
| player1 | player2 | team1 | team2 | date |
+-----------+----------+---------+-----------+------------+
| Bob Smith | John Doe | My Team | Your Team | 2001-05-01 |
There are a lot of other things to consider - it's not impossibly complex, but there are many things involved. You need to do a lot of planning so you know how everything will work together. If, as you said above, you're not very familiar with programming, you would definitely need to learn before you attempted something like this.
igotregistered
11-17-2012, 10:04 PM
Hi Traq, thank you for your explanation. Yes in fact the data is stored exactly as your reference
table
| Title |
+--------------------------------------------------------------+
| Bob Smith vs John Doe May 2, 2001 Sports team vs Sports team |
I'm not actually looking to be as detailed as your second reference. All I want is for whoever I make a customized page for, ie. "Bob Smith". Just for his fights to populate in certain order. Just as long as they populate on his custom page. every time one of his fights are added in the video portal. The video portal is a script I bought, and it's ioncubed. So there's no way for me to alter how data is entered when a new video is added.
Thank you
not actually looking to be as detailed as your second reference. All I want is for whoever I make a customized page for, ie. "Bob Smith". Just for his fights to populate in certain order. Just as long as they populate on his custom page...
It's not a matter of being "detailed" or having "fancy" features: properly organizing the data (as in my second example) makes it possible to reliably search for & find the correct records:
SELECT whatever FROM the_table WHERE the_field = 'Bob Smith'
However, if all the data is lumped together in one field, you can't do that. You'd have to do
SELECT whatever FROM the_table
WHERE the_field = 'Bob Smith' #maybe just the name?
OR the_field LIKE 'Bob Smith%' #maybe the name at the beginning?
OR the_field LIKE '%Bob Smith' #maybe the name at the end?
OR the_field LIKE '%Bob Smith%' #maybe the name in the middle?...and even then, there might be conditions where the search would still fail (or return bad results).
...Yes in fact the data is stored exactly as your reference...
What does the rest of the table look like? How are the videos actually stored/found? You might be able to create a work-around (or parse and reorganize the data into something usable), but that would be less reliable (maybe not possible at all).
Setting aside the "portal script," you need to know both a) what information you need, as well as b) what information is available to you, before you can start this project. Then, you can start working out how to approach your goal. Right now, you have nowhere to start from. You're in for a big learning experience.
**********
p.s.
The video portal is a script I bought, and it's ioncubed.
My sincere advice is to stay away from scripts that are "encrypted." Transparency is a sign of a good program. The more time an author spends on obfuscation, the less time they're spending on making the script good. (The simple fact that the script stores its data in one big lump in the DB makes me question its quality.) Obfuscation also adds another layer of complexity, is another source of bugs or instability, slows program execution down, and just generally looks "shady." Don't fool yourself: they are not trying to "protect their hard work." They are trying to lock you into returning to them (and paying again!) every time you want something changed or need something fixed.
igotregistered
11-18-2012, 12:34 PM
Hmmm, some interesting info there traq. Maybe I shouldn't venture off into using a script then.
So it appears my only option is to manually code each players video, on their own page? I was hoping I could automate it someway so that I didn't have to manually add each fight every time they have one. If you have any other suggestions to some how automate the process, I'm willing to try.
Thank you
Well, I'm certainly not trying to "scare you away" from the idea - I was sincere in saying that it would be a learning experience (A Good Thing), and that I'd be happy to help you along the way. I'm just trying to help you understand that it's more complex than it might appear, and that you may need to do more preparatory work than you imagined (including a fair amount of "basics" with PHP and MySQL).
So far, we need more info about how the videos, and the information about them, are stored. And your original question (about the error message) has gotten sidetracked. Let me know what you'd like to do!
igotregistered
11-18-2012, 04:35 PM
Ok, I'd like to give it a shot then. I've sent a message to the developer of vbtube, (my video portal script) asking him which table(s) are used to store the video data. I appreciate you working with me trying to figure this out. Once I hear back I will post the table info. Thank you
does your host provide database management software (e.g., phpMyAdmin)? You can see the table structure from there.
igotregistered
11-18-2012, 07:01 PM
Yes it does. the table "_vbullthread" is the table where all of the videos are stored.
In phpMyAdmin, you can get the table structure by going to the database in question, clicking on the [SQL] tab, and entering this query:
SHOW CREATE TABLE `_vbullthread`
just post the result here.
igotregistered
11-18-2012, 07:28 PM
This is what I got
CREATE TABLE `_vbullthread` (
`threadid` int(10) unsigned NOT NULL auto_increment,
`title` varchar(250) NOT NULL default '',
`firstpostid` int(10) unsigned NOT NULL default '0',
`lastpostid` int(10) unsigned NOT NULL default '0',
`lastpost` int(10) unsigned NOT NULL default '0',
`forumid` smallint(5) unsigned NOT NULL default '0',
`pollid` int(10) unsigned NOT NULL default '0',
`open` smallint(6) NOT NULL default '0',
`replycount` int(10) unsigned NOT NULL default '0',
`hiddencount` int(10) unsigned NOT NULL default '0',
`deletedcount` int(10) unsigned NOT NULL default '0',
`postusername` varchar(100) NOT NULL default '',
`postuserid` int(10) unsigned NOT NULL default '0',
`lastposter` varchar(50) NOT NULL default '',
`dateline` int(10) unsigned NOT NULL default '0',
`views` int(10) unsigned NOT NULL default '0',
`iconid` smallint(5) unsigned NOT NULL default '0',
`notes` varchar(250) NOT NULL default '',
`visible` smallint(6) NOT NULL default '0',
`sticky` smallint(6) NOT NULL default '0',
`votenum` smallint(5) unsigned NOT NULL default '0',
`votetotal` smallint(5) unsigned NOT NULL default '0',
`attach` smallint(5) unsigned NOT NULL default '0',
`similar` varchar(55) NOT NULL default '',
`prefixid` varchar(25) NOT NULL default '',
`taglist` mediumtext,
`vbseo_linkbacks_no` int(10) unsigned NOT NULL default '0',
`ttype` int(10) unsigned NOT NULL default '0',
`autoskip` smallint(6) default '0',
`description` varchar(250) NOT NULL default '',
`vbtpost` int(2) unsigned NOT NULL default '0',
`vbseo_likes` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`threadid`),
KEY `postuserid` (`postuserid`),
KEY `pollid` (`pollid`),
KEY `forumid` (`forumid`,`visible`,`sticky`,`lastpost`),
KEY `lastpost` (`lastpost`,`forumid`),
KEY `dateline` (`dateline`),
KEY `prefixid` (`prefixid`,`forumid`),
KEY `replycount` (`replycount`),
KEY `visible` (`visible`),
KEY `open` (`open`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=37393 DEFAULT CHARSET=utf8
That doesn't seem to reference the videos themselves (in fact, that looks more like a native part of VBulletin, not a table for an external script...?). Are there any tables in your DB that contain fields that look like URLs to videos?
igotregistered
11-18-2012, 09:01 PM
the script is called "vbtube professional". It is basically a plugin for vbulletin. A very very large plugin. It's not standalone. It requires a full vbulletin infrastructure in order to work.
understood... but still, I don't see anything that references videos in the table you posted - it looks more like it deals with the threads themselves. Can you find any tables in your DB that hold values that look like URLs to videos?
igotregistered
11-18-2012, 09:27 PM
Ok, I'll check
igotregistered
11-18-2012, 09:46 PM
This is the table with the URLS. '_vbullpost'
SQL result
Host: 10xxxxxxxxxx
Database: nhlfightclub
Generation Time: Nov 18, 2012 at 02:41 PM
Generated by: phpMyAdmin 2.11.11.3 / MySQL 4.1.24-log
SQL query: SHOW CREATE TABLE `_vbullpost`;
Rows: 1
Table Create Table
_vbullpost CREATE TABLE `_vbullpost` (\n `postid` int(10) unsigned NOT NULL auto_increment,\n `threadid` int(10) unsigned NOT NULL default '0',\n `parentid` int(10) unsigned NOT NULL default '0',\n `username` varchar(100) NOT NULL default '',\n `userid` int(10) unsigned NOT NULL default '0',\n `title` varchar(250) NOT NULL default '',\n `dateline` int(10) unsigned NOT NULL default '0',\n `pagetext` mediumtext,\n `allowsmilie` smallint(6) NOT NULL default '0',\n `showsignature` smallint(6) NOT NULL default '0',\n `ipaddress` varchar(15) NOT NULL default '',\n `iconid` smallint(5) unsigned NOT NULL default '0',\n `visible` smallint(6) NOT NULL default '0',\n `attach` smallint(5) unsigned NOT NULL default '0',\n `infraction` smallint(5) unsigned NOT NULL default '0',\n `reportthreadid` int(10) unsigned NOT NULL default '0',\n `ame_flag` tinyint(4) NOT NULL default '0',\n `post_thanks_amount` int(10) unsigned NOT NULL default '0',\n `vbtpost` int(2) unsigned NOT NULL default '0',\n PRIMARY KEY (`postid`),\n KEY `userid` (`userid`),\n KEY `threadid` (`threadid`,`userid`),\n KEY `dateline` (`dateline`),\n KEY `visible` (`visible`),\n FULLTEXT KEY `title` (`title`,`pagetext`)\n) ENGINE=MyISAM AUTO_INCREMENT=76515 DEFAULT CHARSET=utf8
This is what the table data looks like when browsing
Full Texts postid threadid parentid username userid title dateline pagetext allowsmilie showsignature ipaddress iconid visible attach infraction reportthreadid ame_flag post_thanks_amount vbtpost
Edit Delete 22 20 0 442nd 1 John Kordic vs. Basil McRae 1202501278 [img]http://img.youtube.com/vi/hpXsS56jBUk/default... 1 0 64.94.199.9 0 1 0 0 0 2 0 0
igotregistered
12-03-2012, 01:40 AM
Ok, I've been researching and I found a script which works....However, it's pulling every single record. I want the script to parse the "title" column and only retrieve the records of player names.
Example
The column "title" in the table has "Bob Smith vs John Doe Apr 1, 2012 hockey team vs hockey team". I want only records which have the name "John Doe" in it. I don't want, any records besides John Doe's.
<?php
// Make a MySQL Connection
mysql_connect("10.x.xx.xx", "un", "pw") or die(mysql_error());
mysql_select_db("mydbname") or die(mysql_error());
// Get all the data from the "_mytables" table
$result = mysql_query("SELECT * FROM _mytables")
or die(mysql_error());
echo "<table border='1'>";
echo "<tr> <th>Fight Matchup</th> <th>Fight Video</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['title'];
echo "</td><td>";
echo $row['link'];
echo "</td></tr>";
}
echo "</table>";
?>
Thank you for any help. It's greatly appreciated.
SELECT `title` FROM `_mytables`
Sorry for the lack of response earlier; I missed your last post. I'm glad you found something that works!
igotregistered
12-03-2012, 12:05 PM
Hi traq, while I am able to find all data in the "title". I only want to find. I only want my query to find a single name in the title, (not the whole title)
So if this is the title : "Bob Smith vs John Doe Apr 1, 2012 hockey team vs hockey team"
I only want to find any records in the table related to "John Doe". Nothing else. HOpe that makes sense.
Thank you
djr33
12-03-2012, 01:51 PM
You can add LIKE '%John Doe%' to your search query. (Use Google to find out more information.)
Note: different servers implement caseless or case-sensitive searching with LIKE. So be aware of that if it matters to you. My impression is that many/most servers are case-insensitive, so it won't matter. But it's an option to have it be case-sensitive.
What you're asking isn't anything particularly obscure in PHP/MySQL, but it does require understanding the basics. You should be able to find tutorials for these things.
From what I can see in this thread, it's time for you to invest in learning these languages so that you can do it yourself. It's far from impossible, although it might take you some time.
igotregistered
12-03-2012, 05:55 PM
Hi djr33. All I want to do is have my page automatically update with the fights from one player. I've been trying to figure this out for weeks.
djr33
12-03-2012, 06:47 PM
That's sort of like saying that you just want to fly a plane from New York to Los Angeles one time, so you don't want to take flying lessons. Alternatively you can ask someone to fly the plane for you.
Anyway, I really am trying to be helpful here. You can use the LIKE keyword as needed. Any MySQL page with "LIKE" on it will have clear examples. Something like this:
SELECT `title` FROM `_mytables` WHERE `title` LIKE '%John Doe%'
This sounds very much like the same question we were discussing earlier:
if all the data is lumped together in one field, you can't do that...
SELECT whatever FROM the_table
WHERE the_field = 'Bob Smith' #maybe just the name?
OR the_field LIKE 'Bob Smith%' #maybe the name at the beginning?
OR the_field LIKE '%Bob Smith' #maybe the name at the end?
OR the_field LIKE '%Bob Smith%' #maybe the name in the middle?...and even then, there might be conditions where the search would still fail (or return bad results).
Did you try a query like this (without the comments)?
igotregistered
12-04-2012, 12:23 AM
It seems like I've upset you guys. I'm sorry. I really only wanted to do one query for my website. I am a designer, unfortunately I know nothing about the database end. I would love to learn it, but it's enough trying to keep up with design. I apologize if I offended anybody, it wasn't meant to be such. All I wanted to do was make custom pages for these players without having to code each fight one by one. Now its working. Thank you all for your help. I really appreciate it.
djr33
12-04-2012, 01:32 AM
You haven't really upset us, but it's pretty clear that you need to learn how to use databases-- and that's not a bad thing. I'm glad it's working. Maybe this is really the only time you'll ever need to use MySQL. But I doubt it. This forum is designed to point people in the right directions rather than write code for you.
Absolutely, no problem. I'm not offended or upset either.
We're here because we like helping people with these kinds of problems. It's not an intrusion.
Really, it's only frustrating when someone expects us to provide a complete solution (possibly on a deadline) while requiring no effort from them.
(To everyone reading this thread, if that last sentence sounds like you, take heed! You're not looking for help, you're looking to HIRE (http://www.dynamicdrive.com/forums/forumdisplay.php?30)!)
If you put forth the effort - even if it's just the effort to understand the answers you're given - we're happy to help. You're learning something, and that is our goal. Don't be apologetic. As Daniel says, needing to learn something is Not a Bad Thing.
-------------------
Having said that, I would sincerely encourage you to learn about this sort of stuff. PHP and MySQL are ubiquitous on the internet; knowing the basics can be nothing but an advantage. If you really are in a position where you can't learn [for now], you would do well to find a good back-end developer that you can build a relationship with and go to for one-off tasks like this.
Powered by vBulletin® Version 4.2.2 Copyright © 2021 vBulletin Solutions, Inc. All rights reserved.