visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
294 experts, 1172 registered users, 1625 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


The DDRK link is back online.
We got a one week service interruption, sorry.
(23-30/01/2010)

Languages :: PHP :: dynamic mysql field resolution
By: jayrod U.S.A.  Date: 23/07/2003 00:00:00  English  Points: 70 Status: Answered
Quality : Excellent
Not sure if this is possible but I'd like to know a way or a strategy for changing queries to reflect new fields easily.

i.e. I have a form with input that matches field names of a database. I then use the data from the form to formulate an insert query.

Later on down the road I add a column to the database. now I have to go back and change my code.

Any way around this?
By: sanjaykattimani Date: 23/07/2003 14:51:00 English  Type : Comment
Following module allows you to execute a query and list the matching fields, additionally it can also execute DML queries
You can alter this code for your use.

<?php
//*********************************************************************************
//
// Module name : Database operations
// Description : Any database operations including updations & listings.
// Author : Sanjay Kattimani
// Date of creation : 01-Jul-2003
// Last update : 10-Jul-2003
// Additional information : None
//
//*********************************************************************************
?>
<html>
<head>
<title>Database operations</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<font face='Arial, Helvetica, sans-serif' size=2>Enter database query below</font>
<body bgcolor="#FFFFFF" text="#000000">
<form name="form1" method="post" action="gdbopr1.php">

<textarea name="Qry" cols="70" rows="5"><?php if (isset($HTTP_POST_VARS['Qry']))
print $HTTP_POST_VARS['Qry'];?></textarea>

<p>
<!-- <input type="submit" name="Submit" value="Go">-->
<input type="image" src="img/Go.gif" border="0" name="Go" alt="Go">
</p>
</form>
<?php
include 'phpDB.php';
print "<font face='Arial, Helvetica, sans-serif' size=2>";
if (!isset($HTTP_POST_VARS['Qry']))
exit();
$qry =$HTTP_POST_VARS['Qry'];
$result = mysql_query ($qry) or die("Error in query : ". $qry. " ".mysql_error()) ;
print "
<b>Query executed successfully.</b>
";


if ($myrow = mysql_fetch_row($result))
{
$fields = mysql_num_fields($result);
// $rows = mysql_num_rows($result);
?>
<table width="75%" border="1">
<?
print "<tr bgcolor='#D7EBFF'> ";
for ($i=0; $i < $fields; $i++)
{
$name = mysql_field_name($result, $i);
?>
<td height="22"><b><font face="Arial, Helvetica, sans-serif" size="2">
<?php print $name; ?></font></b></td>
<?php
}
print " </tr>";


$result = mysql_query ($qry) or die("Error in query : ". $qry ) ;
while ($myrow = mysql_fetch_row($result))
{
print "<tr bgcolor='#F0F8FF'>";
for ($i=0; $i < $fields; $i++)
{
print "<td>";
$fld=mysql_field_name($result, $i);
// print $fld;
print $myrow[$i];
print "</td>";

}
print "</tr>";
}
mysql_free_result($result);
mysql_close();

?>

</table>
<?php
}
?>
</body>
</html>
By: VGR Date: 23/07/2003 16:06:00 English  Type : Comment
yes, the way around is to use a technique like mine (already posted on EEE).

For sure you do as above : DESCRIBE $tablename; or num_fields()+field_name() but I prefer the describe one because I get in one call all I need : length and type of the data (thus I know if I have to quote, or not, the values passed to it), fields' names, etc

Then I build an array of those fields + types + length

Then I offer a FORM to read/load/modify/delete records, based on this (dynamic) layout and with the correct SIZE= and MAXLENGTH= attributes

Then I implemented the actions delete/read/add/modify records

rather easy.

If you want the full source, you'll either have to wait until I'm home this evening (it's 08:10 here, 8:10am if you prefer) or perform a search on EE

regards
By: jayrod Date: 24/07/2003 21:33:00 English  Type : Comment
Ya VGR I'm interested in seeing it. I am getting a new job as a DBA so I'll be upgrading my account soon but right now I don't have full search :P

I really dislike it when php is hardcoded into html. It just unnerves me :P

I'll try out your solution though sanjay.

Thank you
By: VGR Date: 24/07/2003 21:43:00 English  Type : Answer
ok, I decided, not to copy-paste it ***again*** (it's 16KB long), but to make it available worldly as a new free tool on my company's site.

Have a look at <A HREF="http://www.edainworks.com">www.edainworks.com</a> under topics "third-party tools", category "development", name "gestDB.php" and if you don't read French (for the moment), it's also available via <A HREF="http://www.fecj.org/edain/outils/gestDB.php.txt">http://www.fecj.org/edain/outils/gestDB.php.txt</a>

(rename the .txt in .php or right-click/saveAs...)
By: jayrod Date: 24/07/2003 21:56:00 English  Type : Comment
Well I love the end result but I just need.. part of it.. heh the formatting is horrid through the text file you showed. So i'm curious.

Can you kind of point me to the gist of it? (also the french variables are distracting :P)

I'm looking to create a class or a maybe a few methods that will accept a table name and return an associative array of fieldname with the type next to it.

I'm sure that that's in your code somewhere but the html and french are hiding it.

Any line number would be great.

Thanks
By: VGR Date: 24/07/2003 22:03:00 English  Type : Comment
the formatting is perfect :D
By: VGR Date: 24/07/2003 22:07:00 English  Type : Comment
the important part (data building) appears at "if (!isset($table)) {" (around line 131)

the data describing is at line 165

and an example of its use is the "get" action at line 229

while an example of displaying using those data is at line 287 :
(translated)

echo "<tr><td>field name</td><td>type</td><td>kind</td><td>value</td></tr>";

for ($i=1; $i<$globNbChamps; $i++){ // skip 0 (see above)
if ($globChTyp[$i]=='text') echo "<TR><TD>".$globChNom[$i]."</td><TD>".$globChTyp[$i]."</td><TD>".(($globChAcc[$i]==1)?'texte':'numérique')."</td><TD><TEXTAREA NAME=F".$globChNom[$i]." ROWS=6 COLS=60 WRAP=\"soft\" MAXLENGTH=400>".${'loc'.$globChNom[$i]}."</TEXTAREA></td></tr>";
else echo "<TR><TD>".$globChNom[$i]."</td><TD>".$globChTyp[$i]."</td><TD>".(($globChAcc[$i]==1)?'texte':'numérique')."</td><TD><INPUT border=0 TYPE=text NAME=F".$globChNom[$i]." VALUE=\"".${'loc'.$globChNom[$i]}."\" SIZE=".Min($globChLon[$i],70)." MAXLENGTH=".$globChLon[$i]."></td></tr>";
} // for

rather concise, no ?
By: jayrod Date: 24/07/2003 22:11:00 English  Type : Comment
ya give me a few hours to sift and sort :P

Thank you very much.

I'll take a look at this when I get home. hmmm.. I'll post my function when it's done. I would think it may make someone's life easier.
By: jayrod Date: 25/07/2003 01:43:00 English  Type : Comment
function table_info($table_name){
$array = array();

$query = "describe " . $table_name;
$result = $this->dbi->query($query);

while ($row = $result->fetchRow()){
$array[$row->Field] = $row->Type;
}

return $array;
}


The dbi variable is my way of using a database handler in order to execute the query. I'm sure you all get the gyst of it though.

Thank you very much

Hey VGR interested in seeing my application framework? it's really kinda nifty :)
By: VGR Date: 25/07/2003 01:48:00 English  Type : Comment
why not ? But I'm afraid I am totally impermeable to OO, as is Master Niklaus :D
By: jayrod Date: 25/07/2003 01:53:00 English  Type : Comment
It's too big to post here. Give me a min and I'll put it on my site for download.

I too was afraid of OO but after I got used to it, turning out applications is a snap.
By: VGR Date: 25/07/2003 01:58:00 English  Type : Comment
I do the exact contrary whenever I can

I love efficiency :D
By: jayrod Date: 25/07/2003 03:42:00 English  Type : Comment
<A HREF="http://www.dirtywings.com/mutt_frame.tar.gz">www.dirtywings.com/mutt_frame.tar.gz</a>

bah :P

Thanks again for the help. Maybe this'll help someone else out as well

Do register to be able to answer

 Add This Article To:
 del.icio.usDel.icio.us  diggDigg  googleGoogle  spurlSpurl
 blinkBlink  wongWong  simpySimpy  yahooY! MyWeb 
EContact
browser fav
page generated in 71.793080 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page