Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

how to create a hyperlink to execute a mysql query

I want to create a hyper link that will when clicked on, execute a MySql query.  I have attached 3 jpgs to illustrate.  On the samples_list.jpg page I want the the entries under "chema" column to be all hyperlinks that when you click on them, it takes you to the chemical_view.jpg page for that specific chemical.  For example, on the last roll on the sample_list.jpg page the chemical "PHOSPHOMOLYBDIC ACID HYDRATE" I want to be a hyperlink, that when you click on the hyperlink it opens a new window that queries the chemical table (chemicals_list.jpg) and the result would be the chemcials_view.jpg page that queries all the fields for the specific chemical from the chemicals table.
sample-list.JPG
chemicals-view.JPG
chemicals-list.JPG
0
mbaker100196
Asked:
mbaker100196
  • 11
  • 10
1 Solution
 
Ray PaseurCommented:
Is the field "ChemID" consistent in all of the tables?
0
 
mbaker100196Author Commented:
"ChemID" is unique and only used in the chemicals table.
0
 
Ray PaseurCommented:
It appears to be in the JPG called "chemicals-list" -- is "chemicals-view" a separate table or just a view?

What I was hoping to find was that it was present in the JPG called "sample_list"...

Can you please tell me the names of the tables?  Thanks, ~Ray
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mbaker100196Author Commented:
chemicals-list and chemicals are just views.  the two main tables in the database are chemicals and samples.  Other tables in the database are containers, locations, units, users, and vendors. I know I can use the chemicals_view.php but I have not figured out what the code should be for the specific chemcal.

I think maybe in the URL is should be chemicals_view.php?editid1=?  where the "?" is the "ChemID".  I just don't know what the code should for the hyperlink for that specific chemical.

0
 
Ray PaseurCommented:
Regarding this: "I think maybe in the URL is should be chemicals_view.php?editid1=?" - your instincts are right.  I'll try to provide a code sample for you. ~Ray
0
 
Ray PaseurCommented:
OK, I've looked it over and it appears that we are missing a common key between the data base tables.  The only field in common appears to be Chemicals+Chemical_Name = Samples+Chema.  Since this is a free-form text field, it may not be a useful good key -- a misspelling could destroy the relationship between the tables.

A better solution would include a table that carried the Chemid (as a key) and the Chemical Name.  Then the tables that referred to a particular chemical would carry the Chemid field.  A query that needed to relate these tables would be able to make its SELECT statements using the Chemid field to establish the relationship.  This might be a bit of an application rewrite, and that may not be a small amount of work, but it is the right thing to do, and the sooner the better.

See code snippet.  This strategy would work once the tables have the required common keys.

HTH, ~Ray
<?php // RAY_temp_chemistry.php
 
// ASSUME WE ARE CONNECTED TO THE DATA BASE SERVER AND THE CORRECT DATA BASE IS SELECTED
/* * SAMPLE CONNECTION CODE 
 * $db_host	= "dbh"; // PROBABLY LOCALHOST
 * $db_name	= "dbn";
 * $db_user	= "uid";
 * $db_word	= "pwd";
 * 
 * // CONNECT TO THE DATA BASE SERVER
 * if (!$db_connection = @mysql_connect("$db_host", "$db_user", "$db_word")) 
 * {
 *    $errmsg = mysql_errno() . ' ' . mysql_error();
 *    die($errmsg);
 * }
 * 
 * // SELECT THE DATA BASE
 * if (!$db_sel = @mysql_select_db($db_name, $db_connection)) 
 * {
 *    $errmsg	= mysql_errno() . ' ' . mysql_error();
 *    die($errmsg);
 * }
 */ // END OF DB CONNECTION
 
 
// IF THERE IS ANYTHING IN THE QUERY STRING
if (isset($_GET["Chemid"]))
{
 
// USE ONLY POSITIVE INTEGER VALUES FOR QUERY
   $chemid = ereg_replace("[^0-9]", "", $_GET["Chemid"]));
   if ($chemid != $_GET["Chemid"])
   {
      die("Chemid = {$_GET["Chemid"]} NOT A VALID KEY");
   }
   
// IF NO CHEMICAL WAS SELECTED (Chemid == 0)
   if ($chemid == '0')
   {
      die("PLEASE GO BACK AND CHOOSE A CHEMICAL");
   }
 
// CONSTRUCT AND EXECUTE QUERY
   $ssql = "SELECT * FROM Chemicals WHERE Chemid = $chemid"; // MAYBE LIMIT 1 IF Chemid IS UNIQUE?
   if (!$s = mysql_query($ssql))
   {
      echo "<br/>QUERY FAILED: $ssql <br/>\n";
      echo mysql_errno();
      die( mysql_error() );
   }
 
// IF NO DATA WAS FOUND TO MATCH Chemid
   if (!mysql_num_rows())
   {
      die("NOTHING FOUND FOR $chemid");
   }
 
// ITERATE OVER THE RESULT SET
   while ($row = mysql_fetch_assoc($s))
   {
      echo "<pre>";
      var_dump($row); // VISUALIZE OUTPUT
      echo "</pre>";
   }
 
// END OF PROCESSING FOR URL QUERY STRING
}
 
 
 
 
// CREATE THE SELECT LIST FOR THE SAMPLES -- ASSUMES THAT THE KEY Chemid IS PRESENT IN THE Samples TABLE
echo "<form method=\"get\">\n";
 
// CONSTRUCT AND EXECUTE QUERY
$fsql = "SELECT Chema, Chemid FROM Samples ORDER BY Chema ASC";
if (!$f = mysql_query($fsql))
{
   echo "<br/>QUERY FAILED: $fsql <br/>\n";
   echo mysql_errno();
   die( mysql_error() );
}
 
// CREATE A SELECT LIST
echo "<select name="Chemid">\n";
echo "<option value=\"0\">CHOOSE A CHEMICAL \n";
while ($row = mysql_fetch_assoc($f))
{
   echo "<option value=\"{$row["Chemid"]}\">{$row["Chema"]} \n";
}
echo "</select>\n";
echo "<input type=\"submit\">\n";
echo "</form>\n";
?>

Open in new window

0
 
mbaker100196Author Commented:
Thanks Ray!  I will give it a try.  I am a systems person and not a programmer, so I have to muddle my way through the code.
0
 
Ray PaseurCommented:
Understood.  Please post back here with any Qs.  If you want a good intro on PHP and MySQL, grab a copy of this book: http://www.sitepoint.com/books/phpmysql1/

Best regards, ~Ray
0
 
mbaker100196Author Commented:
What text editor are you using?  I want to copy and past the code into my text editor but want to be able to keep the different color fonts.

0
 
Ray PaseurCommented:
The font colors are provided by the EE web site code highlighter.  I just use Textpad - it does code highlighting, as do many editors and IDEs, but the colors you may see here are from EE, not me.
0
 
mbaker100196Author Commented:
Thanks!

Here is the "mysqldump" of the two tables in my database.  Are you saying I should create a third table and put only the chemname and chemid in the new table?


-- MySQL dump 9.11
--
-- Host: localhost    Database: lims
-- ------------------------------------------------------
-- Server version	4.0.20a-debug
 
--
-- Table structure for table `chemicals`
--
 
CREATE TABLE chemicals (
  chemname varchar(200) default NULL,
  CAS varchar(200) default NULL,
  MSDS varchar(200) default NULL,
  COA varchar(200) default NULL,
  Catalog varchar(50) default NULL,
  LOT varchar(50) default NULL,
  WhoOrdered varchar(50) default NULL,
  WhenOrdered date default NULL,
  Vendor varchar(50) default NULL,
  Units varchar(50) default NULL,
  Quantity int(11) default NULL,
  Location varchar(50) default NULL,
  ExpirationDate date default NULL,
  chemid int(11) NOT NULL auto_increment,
  Purity varchar(50) default NULL,
  Mixture varchar(50) default NULL,
  container varchar(50) default NULL,
  PRIMARY KEY  (chemid)
) TYPE=MyISAM;
 
--
-- Table structure for table `samples`
--
 
CREATE TABLE samples (
  samplename varchar(20) default NULL,
  Formulaweight int(11) default NULL,
  chemicalformula varchar(50) default NULL,
  molecularweight int(11) default NULL,
  sampleID int(11) NOT NULL auto_increment,
  chema varchar(200) default NULL,
  chem2 varchar(200) default NULL,
  chem3 varchar(200) default NULL,
  chem4 varchar(200) default NULL,
  chem5 varchar(200) default NULL,
  chem6 varchar(200) default NULL,
  chem7 varchar(200) default NULL,
  chem8 varchar(200) default NULL,
  chem9 varchar(200) default NULL,
  chem10 varchar(200) default NULL,
  datecreated date default NULL,
  chem1quantity int(11) default NULL,
  chem2quantity int(11) default NULL,
  chem3quantity int(11) default NULL,
  chem4quantity int(11) default NULL,
  chem5quantity int(11) default NULL,
  chem6quantity int(11) default NULL,
  chem8quantity int(11) default NULL,
  chem7quantity int(11) default NULL,
  chem9quantity int(11) default NULL,
  chem10quantity int(11) default NULL,
  COA varchar(50) default NULL,
  unitsa varchar(50) default NULL,
  units2 varchar(50) default NULL,
  units3 varchar(50) default NULL,
  units4 varchar(50) default NULL,
  units5 varchar(50) default NULL,
  units6 varchar(50) default NULL,
  units7 varchar(50) default NULL,
  units8 varchar(50) default NULL,
  units9 varchar(50) default NULL,
  units10 varchar(50) default NULL,
  PRIMARY KEY  (sampleID)
) TYPE=MyISAM;

Open in new window

0
 
Ray PaseurCommented:
That might be part of it. This is a data base design issue that is fairly narrow, but a mile deep in terms of its implications for the usefulness and integrity of the data base.  An axiom of DB design is "Do not repeat yourself" so the best approach is to isolate fields wherever feasible and connect them with the queries.

In the "chemicals" table, "chemid" is the only field that is unique.  In the "samples" table, "sampleID" is the only field that is unique.  There needs to be another field, almost certainly in "samples" that tells you what the chemical is.  This is called a "foreign key" because it is a key into a different table, to wit, the "chemicals" table.  Using this key, you can look at a row of data in "samples" and then look up information about the "chemicals" where this key matches.

I do not know enough about your operations there to give you sage advice about the design of this data base, but I am sure you are at the point now where you want to go hire an expert in data base design to come on-site, learn your operations and show you the schema for the data base.  It may cost you a few thousand now, but it will save you from a big problem down the road.

Best regards, ~Ray
0
 
mbaker100196Author Commented:
I understand what you are trying to tell me to do with not repeating fields in the tables.  I assume you mean that I should "normalize" the database.  The problem is if you will look at the samples_list.jpg the samples are composed of up to 10 different chemicals, each with it's own chemid.  

Also, how do I create the hyperlink that executes the query?

Thanks for all your help.
0
 
Ray PaseurCommented:
"... composed of up to 10 different chemicals, each with it's own chemid."

Exactly, and instead of the chemical names, the table should be carrying the chemid fields.  If you depend on the names for the data base lookup, you WILL get errors because of typographical errors in the names.

"...how do I create the hyperlink that executes the query?"  Install the script I posted above, put in the data base connectivity and run it.  I think there is some confusion about the term "hyperlink" - You might be better off with a drop-down select list.  That's what I was aiming for in the script I posted.  A hyperlink would, of necessity, only be able to query one chemical.  

HTH, ~Ray
0
 
mbaker100196Author Commented:
I understand I think.  If you look on the attached jpg that shows the add sample to the samples table, when you select the chemical it auto-fills the units.  I will add another field that will be a drop down for the chemid that when you select the chemical it will auto-fill the chemid also.  Then once the sample is created I can go back to the samples_list.php and add the code your reference in  your last remark.  Once the chemid is auto filled it would be nice to click on it as a link from the samples_list.php page and then do the query for the chemical-view.jpg.

Do you understand what I am trying to do?


samples-add1.JPG
0
 
mbaker100196Author Commented:
Is there an application I can buy that will allow me to create forms to use with php and Mysql?
0
 
Ray PaseurCommented:
You can probably find that forms-to-table ability in any number of frameworks or CMS systems.  I'm thinking that Joomla and Drupal offer this.  But those systems have their own learning curve and you would still need some depth of experience in HTML, PHP and MySQL to make it work.

Have you considered hiring a developer for this task?  It would almost certainly be less expensive in the long run.  I'm afraid that by answering enormously loaded data base questions in a piecemeal fashion I may not be doing you much good.

best regards, ~Ray
0
 
mbaker100196Author Commented:
I know PHP, HTML, MySql and Apache enough to be dangerous.
0
 
Ray PaseurCommented:
Yes, we have all been there at one time or another!

;-)
0
 
mbaker100196Author Commented:
I added another 10 fields  to the samples table called chemida, chemid2, chemid3...... etc.  That will have a drop down box that when you select the chemical to use on the samples_add.php (samplesaddunitschemid.jpg) it should auto fill the units and chemid into the drop down boxes automatically.  Then after adding the sample you can view on the samples_list.php page (sample-list-chemid146.jpg).  I want the "146" under the chemid column to be a hyper link that will open new window and generate a similar view as samples-view.jpg shown above does.  Also, I have attached the MySql sample table structure (samples.txt)
samples.txt
samplesaddunitschemid.JPG
sample-list-chemid146.JPG
0
 
Ray PaseurCommented:
Please see the grading guidelines here:
http://www.experts-exchange.com/help.jsp#hi403

Any reason why you marked this down to a "B" answer?  Please explain, thanks. ~Ray
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now