[SOLVED] SHOW TABLES only getting 1st table...

RIRedinPA · October 21, 2009

I got a db with about 40 tables, I want to list these in a drop down for users. I'm getting the correct number of results but I am parsing the data wrong and only getting the first table. Not sure what I am doing wrong here.


        $query = "SHOW TABLES"; 
$result = mysql_query($query);
$a=0; 
while($showtablerow = mysql_fetch_array($result)) {
	$tablename = $showtablerow[$a];
	//$tablenamearray = explode("_", $tablename); 
	$emparray[] =  $tablename;
	$a++; 
}

I use $emparray to then build the SELECT html...

cags · October 21, 2009

$query = "SHOW TABLES";
$result = mysql_query($query);
$emparray = array();

while($showtablerow = mysql_fetch_array($result)) {
   $emparray[] = $showtablerow[0];
}

RIRedinPA · October 22, 2009

$query = "SHOW TABLES";
$result = mysql_query($query);
$emparray = array();

while($showtablerow = mysql_fetch_array($result)) {
   $emparray[] = $showtablerow[0];
}

Thanks Cags but I had previously tried $showtablerow[0] and got the same result. I get 40 results but all with the 1st table name.

cags · October 22, 2009

Are you saying you have tried that block of code and it's not working?

RIRedinPA · October 22, 2009

Are you saying you have tried that block of code and it's not working?

I had something similar before and it didn't work. I plugged your code in and it didn't work either, same results, I'm only getting the first table name.

The tables are named by employee last name and ISO Week # so they look like this:

Jones_0751

Jones_0752

Jones_0801

Jones_0802

...

Jones_0943

Smith_0751

Smith_0752

...

and so on...

I get the right number of results but only the first table name - so my result list looks like this:

Jones_0751

Jones_0751...

RIRedinPA · October 22, 2009

Here's the full code I am now using:

include "config.php";

$thistask = $_GET['thistask'];

if ($thistask == "show1") { 

$emparray = Array();

//get employee list
$tableselect = "<fieldset><select name=\"activitysheets\" onchange=\"loadissuevalues(this.value, '$magcode');\"><option value=\"null\">Select An Issue Date</option> ";
$emparray = array();

$query = 'SHOW TABLES FROM RecAdDept';
$result = mysql_query($query) or die ("Cannot get tables\n\n" . mysql_error());
while($table = mysql_fetch_row($result)) {
	$emparray[] = $table[0]; 
} 


//clean array of duplicates
//$emparray = array_unique($emparray);

//loop through to add options to select html
for ($x=0; $x<count($emparray); $x++) { 
	$tableselect .= "<option value='$emparray[0]'>$emparray[0]</option>";
}

$tableselect .= "</select></fieldset>";

echo $tableselect;

} else { 
echo "show all tables - ug.";
}

Here's my return:

<option value="null">Select An Issue Date</option>

<option value='Jones_0751'>Jones_0751</option>

...

<option value='Jones_0751'>Jones_0751</option>

</select>

</fieldset>

RIRedinPA · October 22, 2009

Here's the whole setup:

Page where the user selects which task they want to perform. I'm not doing anything with the include files yet so they have no affect on output.:


<!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>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Merion Recruitment Ad Department Activity Sheet Manager</title>
<link href="css/radas.css" rel="stylesheet" type="text/css" />

<script language="javascript" src="js/radas.js"></script>
</head>

<body onload="setup();">

<?php 
include ('lib/config.php');
include ('lib/functions.php');
include ('lib/arrays.php');
?>

<div id="header"><a href="index.php?view=new"><img src="images/title.gif" border="0"></a></div>

<div id="leftCol">

    Select a task from the options below:
    <p>
    
    <form name="getTask" method=GET action="index.php">
    <fieldset>
    	<legend>Admin Options</legend>
   	 	<select name="taskChoice" onchange="showemployees()">
    		<option value="null">Select A Task</option>
        	<option value="view1">View Employee</option>
        	<option value="view2">View All Employees</option>
    	</select>
    </fieldset>
    
    <div id="emptable" style="display: none;">
    	 
    </div>
    </form>
</div>

<div id="resultTable"> </div>

</body>
</html>

Javascript to get data, make AJAX call:


// JavaScript Document

function showemployees() { 


//set form
var theform = document.getTask; 

//determine if one or all employees
var taskChoiceSI = theform.taskChoice.selectedIndex;
if (taskChoiceSI != 0) { 
	if (taskChoiceSI == 1) { 
		var thistask = "show1";
	} else { 
		var thistask = "showall";
	}

	var xmlHttp = checkajax();

	xmlHttp.onreadystatechange=function() {
		if(xmlHttp.readyState==4) {
			alert(xmlHttp.responseText);
			//document.getElementById('emptable').innerHTML = xmlHttp.responseText;
			//document.getElementById('emptable').style.display = "block";
		}
	}

	xmlHttp.open("GET", "lib/getemplist.php?thistask=" + thistask, true);
	xmlHttp.send(null);
}



}

function setup() { 
document.getTask.taskChoice[0].selected = true; 
}


function checkajax() {
//ajax compatibility check
	var xmlHttp;
	try {
  			// Firefox, Opera 8.0+, Safari
  			xmlHttp=new XMLHttpRequest();
  		}
		catch (e) {
  			// Internet Explorer
  			try {
    			xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
    		}
  				catch (e) {
    			try {
      				xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
      			}
    				catch (e) {
      				alert("Your browser does not support AJAX!");
      				return false;
      			}
    		}
  		}

	return xmlHttp
}

and the gettemplist.php code, which I also posted above:


<?php 


include "config.php";

$thistask = $_GET['thistask'];


if ($thistask == "show1") { 

$emparray = Array();


//get employee list
$tableselect = "<fieldset><select name=\"activitysheets\" onchange=\"loadissuevalues(this.value);\"><option value=\"null\">Select An Issue Date</option> ";
$emparray = array();

$query = 'SHOW TABLES FROM RecAdDept';
$result = mysql_query($query) or die ("Cannot get tables\n\n" . mysql_error());
while($table = mysql_fetch_row($result)) {
	$emparray[] = $table[0]; 
} 


//clean array of duplicates
//$emparray = array_unique($emparray);

//loop through to add options to select html
for ($x=0; $x<count($emparray); $x++) { 
	$tableselect .= "<option value='$emparray[0]'>$emparray[0]</option>";
}

$tableselect .= "</select></fieldset>";

echo $tableselect;

} else { 
echo "show all table - ug.";
}
?>

DavidAM · October 22, 2009

   //loop through to add options to select html
   for ($x=0; $x<count($emparray); $x++) {
      $tableselect .= "<option value='$emparray[0]'>$emparray[0]</option>";
   }

You have collected the table names in an array ($emparray). In the loop, you are always referring to the first element of the array ($emparray[0]), so you are just getting the first table. Change that to:

   //loop through to add options to select html
   for ($x=0; $x<count($emparray); $x++) {
      $tableselect .= "<option value='$emparray[$x]'>$emparray[$x]</option>";
   }

cags · October 22, 2009

The reason your output is wrong has nothing to do with the original set of code, the code I gave before works perfectly.

for ($x=0; $x<count($emparray); $x++) {
   $tableselect .= "<option value='$emparray[0]'>$emparray[0]</option>";
}

You're always echo'ing out the first item in the array, the 0 should be $x to print out the current item. Having said that the whole content of the if block may just as well be...

$tableselect = "<fieldset><select name=\"activitysheets\" onchange=\"loadissuevalues(this.value, '$magcode');\"><option value=\"null\">Select An Issue Date</option> ";   
$query = 'SHOW TABLES FROM RecAdDept';
$result = mysql_query($query) or die ("Cannot get tables\n\n" . mysql_error());
while($table = mysql_fetch_row($result)) {
$tableselect .= "<option value='".$table[0]."'>".$table[0]."</option>";
}    
$tableselect .= "</select></fieldset>"; 
echo $tableselect;

Eliminating the need for 2 loops.

RIRedinPA · October 22, 2009

The reason your output is wrong has nothing to do with the original set of code, the code I gave before works perfectly.
for ($x=0; $x<count($emparray); $x++) {
   $tableselect .= "<option value='$emparray[0]'>$emparray[0]</option>";
}
You're always echo'ing out the first item in the array, the 0 should be $x to print out the current item. Having said that the whole content of the if block may just as well be...
$tableselect = "<fieldset><select name=\"activitysheets\" onchange=\"loadissuevalues(this.value, '$magcode');\"><option value=\"null\">Select An Issue Date</option> ";   
$query = 'SHOW TABLES FROM RecAdDept';
$result = mysql_query($query) or die ("Cannot get tables\n\n" . mysql_error());
while($table = mysql_fetch_row($result)) {
$tableselect .= "<option value='".$table[0]."'>".$table[0]."</option>";
}    
$tableselect .= "</select></fieldset>"; 
echo $tableselect;
Eliminating the need for 2 loops.

Damn, that was embarrassing... thanks for the help...

cags · October 22, 2009

No problem, your welcome. There is a SOLVED button at the bottom left corner of your screen so people know you've solved the problem.

Sign In

[SOLVED] SHOW TABLES only getting 1st table...

Recommended Posts

RIRedinPA

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

RIRedinPA

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

RIRedinPA

Link to comment

Share on other sites

RIRedinPA

Link to comment

Share on other sites

RIRedinPA

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

RIRedinPA

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information