Solved

Calling MySQL Fields in PHP

Posted on 2013-01-04
12
276 Views
Last Modified: 2013-01-08
Hello,

I'm very new to PHP and MySQL.  I'm trying to troubleshoot a problem on my company's website, where certain fields that should show up are not.  The address doesn't show up, but the city and state do.

In looking at the code, I see where the fields are being called, but none of the field names actually match the field names in the database itself, even those fields which are displaying correctly.

Is there some sort of field-name-aliasing in PHP that I'm missing here?
0
Comment
Question by:Brodie Krause
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38744511
AFAIK, there is no such a system. Maybe the field names are wrapped in constants so you should find somewhere something like define(USEDFIELDNAME, 'databaseFieldName');
but this doesn't seem to be a good practice, nor so usual. At the end of the day, I think this is very strange and if you could post here some code maybe I can help you better...

Cheers
0
 
LVL 10

Assisted Solution

by:jagadeesh_motamarri
jagadeesh_motamarri earned 150 total points
ID: 38744517
In a simple way you would access MYSQL using PHP as follows -

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

Open in new window


But it totally depends if your application is using any php-mysql in-house developed wrapper. But somewhere in the framework you should be seeing the table and the columns that are being fetch as part of the select.
0
 

Author Comment

by:Brodie Krause
ID: 38744650
jagadeesh_motamarri, thanks!  Your sample code pointed me to the intial query for the MySQL db.

Here's the query:
$sql = "SELECT UID AS id, FirstName AS first, LastName AS last,  Location AS loc1, CONCAT(Address,Address2) AS add1, City AS city1, State AS state1, Postal AS zip1, Phone AS phone1, 2Location AS loc2, CONCAT(2Address,2Address2) AS add2, 2City AS city2, 2State AS state2, 2Postal AS zip2, 2Phone as phone2 FROM person ";

Open in new window


It seems this "AS" modifier is translating from the actual DB field name to what the developer wrote the code to call.  However, here's the section of code I'm looking at:
<?php
if($person['Location1'] != '') 
        { 
        $locfields = array('Location','add','city','state','zip','phone');
	for($i=1;$i<=4;$i++) 
                {
		foreach($locfields as $field) 
                        { 
			if(isset($person[$field.$i]) && $person[$field.$i] != '') 
                                { 
				echo $person[$field.$i]."\n<br>\n";
			        }
		        }
	echo "\n<br>\n";
	        }
        }
?>

Open in new window


So strangely, the names being called here don't even match what is being defined in that Query (they lack the numerical identifier).  But, in this case, the Zip code is displaying properly.  If I remove this zip reference, the zip code disappears off the served up page, so I know I'm looking in the right spot, I just fail to see the connection between these references and the db.
0
 
LVL 10

Expert Comment

by:jagadeesh_motamarri
ID: 38744747
This seems to be the right code.

Observe the variable i in the for loop -

for($i=1;$i<=4;$i++) 

Open in new window


and this is what is being concatenated with the fields in the below line -

if(isset($person[$field.$i]) && $person[$field.$i] != '')

Open in new window


so each field is accessed as $field.$i
0
 
LVL 5

Expert Comment

by:Goofytouy
ID: 38744809
Hi... I maybe wrong but... I do not see at any place, how is the $SQL executed, in other words the call of function mysql_query....

That function loads the corresponding resource where information it's going to be saved...
Usually, information is loaded into an array with something as

$fields = mysql_query($Query1) or die ("Database error");

Open in new window


afterwards, you retrieve each row data using the function mysql_fetch_array

$row=mysql_fetch_array($fields)

Open in new window


You should check for some variation of that.


Regards
Goofy
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 50 total points
ID: 38745425
I think your developer is trying to show how clever he is by renaming everything and making it hard to maintain.  I'm currently working on a site with similar problems where the developer made things clever instead of maintainable and it takes forever to figure out how to make changes that don't break the site.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 38746593
@DaveBaldwin: Ever notice how the Venn diagram of "clever" seems to overlap "stupid"? ;-)

@BKCK2k3:  Drop what you're doing.  Buy this book right now and give yourself the time necessary for you to read it and work through the examples.  It is an excellent introduction to the essentials that you need to know.  Without a foundation in PHP and MySQL you might as well be trying to write German poetry without understanding German vocabulary and grammar!
http://www.sitepoint.com/books/phpmysql5/

That said, and in respect of the learning curve ahead of you, you might want to consider hiring a professional PHP developer rather than taking the time to learn PHP (and making all the mistakes along the way).  From what I can see in the little bit of code you've posted here, the existing codebase realizes a technically incompetent design and it will be very hard to add to it successfully.  It may be a faster path to success to discard this code and start over with the right processes.

But if you want to do this yourself, here are some things you can do that will help.

First, understand that it's all about the data.  The only thing computer programs do is transform data from one format to another format.  So naturally you want to visualize the data at every turn in order to see what the programming is doing to it.  This PHP function is your friend: var_dump().

You want to do something like this (probably right now) to see what's retrieved by the query... (and you're right about the use of the AS modifier).

$sql 
= 
"
SELECT 
  UID AS id
, FirstName AS first
, LastName AS last
, Location AS loc1
, CONCAT(Address,Address2) AS add1
, City AS city1
, State AS state1
, Postal AS zip1
, Phone AS phone1
, 2Location AS loc2
, CONCAT(2Address,2Address2) AS add2
, 2City AS city2
, 2State AS state2
, 2Postal AS zip2
, 2Phone as phone2 
FROM person 
"
;
$res = mysql_query($sql) or die("FAIL: $sql WHY: " . mysql_error());
echo '<pre>';
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}

Open in new window

Your data dump will be much easier to read if your script uses echo '<pre>' to set the formatting.  Once you see the data coming back from the query, you can begin to figure out how to display this data predictably.

Empty columns in the query results set are still present and are defined data elements.  You don't have to do anything special with them - just echo them and PHP will send an empty string to the browser.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 38747176
Ever notice how the Venn diagram of "clever" seems to overlap "stupid"? ;-)

Oh yes.   I guess I shouldn't complain.  These events make money for you and me and Cd&.  It seems that the clever ones think no one else will be able to maintain the site that way.

So boys and girls, just keep on with the clever coding.  When it stops working, they will hire one of us older coders to fix it.  Unless it's a Smarty template or Xoops.  Those are real close to being cleverly Wrong even when the are right.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38747343
@DaveBaldwin: Right On!
0
 

Author Comment

by:Brodie Krause
ID: 38751875
@DaveBaldwin:   I had a feeling that was the case, regarding the clever developer, but lacked the foundational knowledge to throw that stone!  From the little bit I've gleaned from the code, my reaction has consistently been, "It's got to be simpler than that!"  After all, why would anyone rely so heavily on PHP if it is really this cumbersome!  :-)  I just stepped into this job a couple months ago, and when the issues with this site were brought to my attention, I was told the developer wanted an additional $3k to fix the problems, or $5k to rewrite the site "using updated standards and practices".   Needless to say, I'm now very motivated to learn this stuff to break our dependence on guys like this.

@Ray_Paseur:   Thanks for the tips and the resource, I've just placed the order and will be checking it out ASAP.   From what I can tell, you're dead on that the site was not written using best practices.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 38751963
Clever code and incompetent code are the two things that keep me in business.  There are some things I won't work on like Smarty and Xoops because they are frameworks that are too complicated to begin with.  But most things can done fairly simply even with a pretty fair amount of detail.  Good luck on your project.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38754392
Thanks for the points, and best of luck with it! ~Ray
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now