Member Avatar for codeorder

Hello everyone. I'm new to this forum and PHP in general.

I finally got the nerve to tackle a mySql Database on my website's server, added columns and a few values, although I cannot seem to find any online help to retrieve the values from the database table.

Here is my test html page.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>mySql beta test</title>
</head>
<body>
    <div id="content">Selected Year:</div>
    <select id="years" onclick="javascript:getValues();" style="font-size:32px;">
        <option>2011</option>
        <option>2010</option>
        <option>2009</option>
    </select>
    <script type="text/javascript">
        function getValues() {
            var i = document.getElementById('years').selectedIndex;
            var selectedYear = document.getElementById('years').options[i].value;
            var selDBitems = '';

            // Need help here to connect to mySql, retrieve all items in table that have the selected year,
            // and write the values to a string.
            //  selDBitems += ?????

            document.getElementById('content').innerHTML = selDBitems;
        }
    </script>
</body>
</html>

Basically, I want to connect to mySql's table, which has 4 columns for: year,name,info,description, and retrieve values.

For this thread and to get my head a little more clear about using php, I only need to retrieve the database items that have the same year as the year selected in the <select> box.

Thanks kindly in advance.

Edited by codeorder because: n/a
  • 2 Contributors
  • 3 Replies
  • 178 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by codeorder
Member Avatar for PsychicTide

Javascript can't directly interact with the database (it's client-side code, you need server-side code executed)

This is just a suggestion, but you could use a form of AJAX to call a pre-made PHP page and pass a variable to that page.

I haven't tested this out yet, but the basic idea is there.
call ajaxContent('something.php', yearSelected) after you find the year selected.

XMLhttp Request

var xmlHttp

function ajaxContent(page, year)
{ 
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
 {
 alert ("Browser does not support HTTP Request")
 return
 }
//set PHP page and pass variable to year
var url = (page + '?year=' + year);
xmlHttp.onreadystatechange=stateChanged 
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}

function stateChanged() 
{ 
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
 { 
 document.getElementById("content").innerHTML=xmlHttp.responseText 
 } 
}

function GetXmlHttpObject()
{
var xmlHttp=null;
try
 {
 // Firefox, Opera 8.0+, Safari
 xmlHttp=new XMLHttpRequest();
 }
catch (e)
 {
 //Internet Explorer
 try
  {
  xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
  }
 catch (e)
  {
  xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
 }
return xmlHttp;
}

something.php

<?php

//set year from passed variable
$Y = year;

//connect to and select MySQL database
mysql_connect("hostname", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());

//Query MySQL for all rows with the year = the passed variable
$result = mysql_query("SELECT * FROM tableName WHERE yearColumnName=$Y ");

//find number of rows and cycle through all rows
$num_rows = mysql_num_rows($result);
$i = 0;
while ($i < $num_rows)
{
  $row = mysql_fetch_array($result);
  //set variables to column data in row
  $name = $row['nameColumnName'];
  $info = $row['infoColumnName'];
  $descr = $row['descrColumnName'];

  //echo however you want
  echo $Y.$name.$info.$descr;
  $i++; 
}
?>

Hope it helps.

Edited by PsychicTide because: n/a
commented: :) +10
Member Avatar for codeorder

I managed to get all the items in my table by using your first posted code.
I just need an If statement here and there to only get the items with the selected year, although overall I'm getting results.

Thanks for all your help. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.