Use Id as Table name in query

Try again: Ok. I’m trying to pass the Id of the element cicked to an Ajax function as the table name which exists on the database. The id of the li element is the same as the table name in the database.

External.js


function getproduct(id){
if(id){
document.getElementById('f1').innerHTML=' ';
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("f1").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getproduct.php?q="+id,true);
xmlhttp.send();
}
 
var jewelry= document.getElementById('jewelry').childNodes;   
for (i = 0; i < jewelry.length; i++) {   
if (jewelry[i].className == 'item') {  // excludes class="item disabled" 
jewelry[i].onclick = function() {   
alert(this.id+': Is sending your request.');  // this actually gets the right id
var id=this.id; 
getproduct();
}   
}   
}

PHP Query


$q=$_GET["id"];
$query=mysql_query("SELECT * FROM '.$q.'")or die(mysql_error( ));
//syntax error at line one or undefined constant is the response

Can the table be refrenced by ‘$q’ ???:rolleyes:

You are requesting an id as a parameter in the getproduct function, but you don’t call the id.

So this:


jewelry[i].onclick = function() {   
	var id=this.id; 
	getproduct();
}; 

Probably just needs to be:

jewelry[i].onclick = function() {   
	getproduct( this.id );
};   

centered effect Thanks for the responce, the constant wasn’t defined as ‘this.id’ and was still recieving all the ids of jewelry. length? Wow, I don’t know how I missed that and I’m prity sure I tried that, declaring ‘this.id’ as a variable and then passing it to the Ajax function so it became a matter of the syntax of select * from ‘id’ for me, and the ‘this.id’ was lost.
Have to check it out on the thumb drive server and will post back latter. Thanks a mill pesos (:

centered effect Wow, I’m getting quicker at transfering files to the thumb drive and runing the sever. Unfortunatley I’m getting a syntax error at line 1 “.silver.” which is the correct id! I’m guessing the httpresponcetext written to div f1 is reffering to the getproduct.php.

getroduct.php


<?php
$q=$_GET["q"];//error in syntax maybe single quotes 'q'
$link=mysql_connect("localhost","root","root")or die(mysql_error( ));

mysql_select_db("bwi",$link) or die(mysql_error( ));

$query=mysql_query("SELECT * FROM '.$q'")or die(mysql_error( ));//maybe double quotes ".$q."

while($row = mysql_fetch_array($query))

{

Will try everything I can before I post back.

Wow I’m still getting this error in the response text.


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''silver'' at line 1

I guess I’ll have to post everything again so that everyone can see what is going on. The MySQL server version is 5.5.15
or phpMyAdmin version. Not that that means anything as the syntax works when I type in select * from silver directly the silver table is accessed and written to the innerHTML of div f1???

centered effect and anybody else looking in. I’m reposting all the new script to see if any one can figure out why I’m still getting this error in the responcetext.

Error:


You have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near "silver" at line 1//reports correct id of each li item clicked

getproduct.php


<?php
$q=$_GET["q"];//syntax to use near "silver" at line 1???

$link=mysql_connect("localhost","root","root")or die(mysql_error( ));

mysql_select_db("bwi",$link) or die(mysql_error( ));

$query=mysql_query("SELECT * FROM ' " . $q . " ' ")or die(mysql_error( ));//added space to show $q syntax clearly

while($row = mysql_fetch_array($query))

{

External.js


function getproduct(id){
if(id==" "){
document.getElementById('f1').innerHTML=' ';
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("f1").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getproduct.php?q="+id,true);//maybe q="+this.id,true ??
xmlhttp.send();
}
 
var sidebar=document.getElementById('sidebar').getElementsByTagName('li'); //sidebar only li elements  
for (i = 0; i < sidebar.length; i++) {   
if (sidebar[i].className == 'item') {  // excludes class="item disabled" 
sidebar[i].onclick = function() {   
alert(this.id+': Is sending your request.');  // this actually gets the right id
var id=this.id; //irrelivant declaration?
getproduct(this.id);
}   
}   
} 

Getting a little verbose but I could really use some fresh eyes as I’ve been stairing at the trees for so long I can’t see the forest.:sick:

All you need is this:

$query = mysql_query("SELECT * FROM ". $q) or die(mysql_error());

The tables doesn’t need quotes:

SELECT * FROM table WHERE column = "something"

Also, if your not already:


function clean($str) {
	if (get_magic_quotes_gpc()) {
		$str = stripslashes($str);
	}
	$query = mysql_real_escape_string($str);
	return $str;
}

$q = clean($_GET["q"]);

centered effect Your kiding! I’ll check it out though I think I might already have. As for the sanitizing function I’m hoping that is to prevent sql injection and maybe it could be added as ‘includes’ just like the $link or $con var. Will get back in a few. Just have to reorganize the script I was throwing an unexpected T_VARIABLE at line 7 which is the select from query line while experimenting with the quotes around the $q on that line:nono:

centered effect Wow I was hoping you where right but I’m still getting a syntax error at line 1 erroniosly stating “FROMsilver” which is the query line 7 as the id is passed as q but select from " .$q) doesn’t work and I’m guessing the php.ini file has something to do with it as when I used your clean function it threw errors telling me that those utilities where not turned on or something of that nature. I’m guessing that the myPhpAdmin is not allowing some of the function you used untill a real password is used to access the database so there might be something I can do to get id recognized as $q maybe php has an alert $q thing that I can throw into the getproduct.php?:x

“FROMsilver” means you are missing the space between “FROM” and “silver” so SQL is noting the error.


$query = "SELECT * FROM ". $q;
echo $query;
$result = mysql_query($query) or die(mysql_error());

What is the $query line echo’ing?

centered effect
This is the getproduct.php snipet I used


$query=mysql_query("SELECT * FROM ".$q)or die(mysql_error());

I have to check again but the FROM_" space is clearly marked. I’ll get back in a minute. Thanks.

centered effect
Glory Hallalujah! It works. Thanks again for your help! It is amazing that MySQL or PHP did not have anything on the select from ‘TABLE’ as var syntax at all. Even the WC3 never addressed it Nor any of the online Ajax tutorials. I’m guessing that only those who had to query the whole table of many tables ever even had to make such a call and could never have guessed the syntax of $q var for the table name. In the end it was the responceText error that pointed it out to centered effect who had to point it out to me “FROMsilver” at line 1 was erronious as it was line 7 but the FROM clearly indicated the query line itself.

For those that may be interested, this is the final script and html snipits. Just remember that the tables in the db must match the ids of the ul li elements in the HTML.
sidebar html


<!--===============================#SIDEBAR DIV============================--> 

<div id="sidebar">

<!--===============================#bags ul================================-->

<ul id="bags" class="prod">
<li class="top" title="Beautiful Bags Made in Mexico!">Bags &amp; Purses</li>
<li class="item disabled" id="leatherbags" title="Coming soon.">Leather</li>
<li class="item" id="syntheticbags" title="Synthetic Bags, Made in Mexico.">Synthetic</li>
<li class="item" id="wovenbags" title="Woven Bags Made in Mexico.">Woven</li>
</ul>

<!--================================#boys ul===============================-->

<ul id="boys" class="prod">
<li class="top" title="Handsom Boys Ware Made in Mexico!">Boys</li>
<li class="item disabled" id="boyaccessories" title="Coming soon.">Accessories</li>
<li class="item disabled" id="boyjackets" title="Coming soon.">Jackets</li>
<li class="item" id="boyshirts" title="Style, Tailoring &amp; Price.">Shirts</li>
<li class="item disabled" id="boyshoes" title="Coming soon.">Shoes</li>
<li class="item disabled" id="boyshorts" title="Coming soon.">Shorts</li>
<li class="item disabled" id="boyslacks" title="Coming soon.">Slacks</li>
<li class="item disabled" id="boysuits" title="Coming soon.">Suits</li>
</ul>

<!--=============================#decor ul===========================-->

<ul id="decor" class="prod">
<li class="top" title="Household furnishing &amp; decoration.">D&eacute;cor</li>
<li class="item" id="art" title="Gloria&rsquo;s Choice.">Art</li>
<li class="item" id="baskets" title="Hand Made by Mexican Artisans.">Baskets</li>
<li class="item" id="books" title="About Mexican Artisans and their crafts.">Books</li>
<li class="item" id="carpetrug" title="Fabulous style and craftsmanship.">Carpets &amp; Rugs</li>
<li class="item" id="dinning" title="Beautiful collection of dinning regalia.">Dinning</li>
<li class="item" id="lamps" title="Standup &amp; sconce candle lamps.">Lamps</li>
<li class="item" id="planters" title="Exquisite Pot and Vase collection.">Planters</li>
</ul>

</div>

<!--=========================END #SIDEBAR DIV=========================--> 

External.js or Ajax.js


function getproduct(p){
if(p==" "){
document.getElementById('f1').innerHTML=' ';
alert(p);
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
alert(p);
}
else{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
alert(p);
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("f1").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getproduct.php?q="+p,true);
xmlhttp.send();
}
 
var sidebarli=document.getElementById('sidebar').getElementsByTagName('li');   
for (i = 0; i < sidebarli.length; i++) {   
if (sidebarli[i].className == 'item') {  // excludes class="item disabled" 
sidebarli[i].onclick = function() {   
alert(this.id+': Is sending your request.');  // this actually gets the right id
var p=this.id; 
getproduct(p);
}   
}   
}

getproduct.php


<?php

$q=$_GET["q"];

$link=mysql_connect("localhost","root","root")or die(mysql_error( ));

mysql_select_db("bwi",$link) or die(mysql_error( ));

$query=mysql_query("SELECT * FROM ".$q)or die(mysql_error());

while($row = mysql_fetch_array($query))

{

echo "<div id='rowcontainer" . $row['id'] . "' class='rowcontainer' title='Scroll to See more'>";
#
#more of your own html
#create html page first to css then eco each line with db table cell refs like .$row['id']. in PHP page

}

mysql_close($link);
?>

As I am a complete nubie please let me know if there is anything that I need to be aware of as far as sql injection etc.
Thanks again centered effect for all your help your name will be mentioned in both the js and the php page.:agree:

OK. I love the function I got with the help of centerd effect but the problem is that some of the uls in sidebar div contain li elements with the class name ‘item’ that when clicked should not be posting a get query to the server. Some actualy grab an html page from the server by writing an object to the innerHTML of div ‘f1’ and another posts a search query to a totaly different php file allowing the client to enter an item code that querys all the product tables in the database and calls the complete table that item is found in to the ‘f1’ div. Unfortunatley these two uls use the same css to style them so that they can expand and colapse on hover. I’d like to write the function so that it takes into account all of this and deels with all the seperate functionality of each li element clicked. No I don’t want to wrap the seperate uls in divs because we would still blote the css and the js/ajax files so I need to add some if and else statements to this function.

External.js/Ajax.js


var sidebarli=document.getElementById('sidebar').getElementsByTagName('li');   
for (i = 0; i < sidebarli.length; i++) {   
if (sidebarli[i].className == 'item') {  // excludes class="item disabled" Need to exclude other uls
//if (sidebarli[i].className == 'item' && sidebar[i].parentNode.className=='prod') { 
sidebarli[i].onclick = function() {   
alert(this.id+': Is sending your request.');  // this actually gets the right id
var p=this.id; 
getproduct(p);
}  
 if (sidebarli[i].className == 'item' && sidebar[i].parentNode.className=='site') { 
sidebarli[i].onclick = function() {   
//different function
} 
 if (sidebarli[i].className == 'item' && sidebar[i].parentNode.className=='code') { 
sidebarli[i].onclick = function() {   
//different function
}
}   
} 

Please let me know if I’m on the right track, I will check it out myself.

Ok so a different item will do something differently. You can bind the function to the element.

Using the previous noted (truncated) HTML & jQuery:

All #bag li.items including disabled


<ul id="bags" class="prod">
	<li class="top" title="Beautiful Bags Made in Mexico!">Bags &amp; Purses</li>
	<li class="item disabled" id="leatherbags" title="Coming soon.">Leather</li>
	<li class="item" id="syntheticbags" title="Synthetic Bags, Made in Mexico.">Synthetic</li>
</ul>

$('#bags li.item').each(function() {
	$(this).bind('click', function() {
		// You ajax function here
	});
});

All #boys li.item items excluding disabled


<ul id="boys" class="prod">
	<li class="top" title="Handsom Boys Ware Made in Mexico!">Boys</li>
	<li class="item disabled" id="boyaccessories" title="Coming soon.">Accessories</li>
	<li class="item" id="boyshirts" title="Style, Tailoring &amp; Price.">Shirts</li>
</ul>


$('#boys li.item:not(".disabled")').each(function() {
	$(this).bind('click', function() {
		// You ajax function here
	});
});

#decor .top element only


<ul id="decor" class="prod">
	<li class="top" title="Household furnishing &amp; decoration.">D&eacute;cor</li>
	<li class="item" id="art" title="Gloria&rsquo;s Choice.">Art</li>
</ul>


$('#decor li.top').each(function() {
	$(this).bind('click', function() {
		// You ajax function here
	});
}); 

Not sure if you can use jQuery, but I wanted to demonstrate the example.

centerd effect jQuery is a library of Javascript that you link your HTML pages to as a relative link to another server and a very powerfull and attractive tool for building websites. Unfortunatley adding relative links to the head of the document and bloating the html page itself with those proprietary links is not my intention at this moment as I am trying to learn Javascript and Jscript at this time. The jQuery site offers a rare combination of the lattest CSS and Javascript. When you use it you add 10 relative links minimal to the head of your html and loose some control of both the rendering/css and the functionality/js. Knowing that you can bind html elements on your pages to a function on an external.js file is what unobtrusive javascript is all about. Linking from your server to another server is also a part of that education and necessary when including Paypals, Verisign, FeDX, Foxycart etc. The list is long and the size of your HTML page has only the limitations of your server and the clients cpu. For now I don’t choose to add any relative external links to my html that I have not built and written myself so that I can acctualy see the result and affect of my own actions and try my best not to rely on some source of higher inteligence beyond the power of my own ability to learn. For educational purposes I would like to know how to create this function myself.

You totally lost me there. jQuery is a JS framework useful for the DOM. I gave the above example as just that, and example of binding an event to a specific element, since you did mention some Li items do one thing and another does another. You gave no specifics, but I gave an example of what you could do.

Here is the same example using plain Javascript with some helpers…


var bagsItem = document.getElementById('bags').getElementsByTagName('li');
for(var i=0; i<bagsItem.length; i++){
	var clsArr = explode(' ', bagsItem[i].className);
	if( in_array('item', clsArr) ) {
		addEvent(bagsItem[i], 'click', function(){
			alert('Bags Item Clicked');
		});
	}
}

var boysItem = document.getElementById('boys').getElementsByTagName('li');
for(var i=0; i<boysItem.length; i++){
	var clsArr = explode(' ', boysItem[i].className);
	if( !in_array('disabled', clsArr) && !in_array('top', clsArr) ) {
		addEvent(boysItem[i], 'click', function(){
			alert('Boys Item Clicked');
		});
	}
}			

var decorTop = document.getElementById('decor').getElementsByTagName('li');
for(var i=0; i<decorTop.length; i++){
	var clsArr = explode(' ', decorTop[i].className);
	if( in_array('top', clsArr) ) {
		addEvent(decorTop[i], 'click', function(){
			alert('Decor Item Clicked');
		});
	}
}		

I used explode, in_array, and addEvent to help me out:
https://raw.github.com/kvz/phpjs/master/functions/strings/explode.js
https://raw.github.com/kvz/phpjs/master/functions/array/in_array.js
http://ejohn.org/projects/flexible-javascript-events/

centerd effect
I’m looking for something like this but I’m screwing up the syntax of the if and else statements I’d guess within the for statement. Maybe they reqire returning true or false?
External.js


var sidebarli=document.getElementById('sidebar').getElementsByTagName('li');   
for(i = 0; i < sidebarli.length; i++) {  
if(sidebarli[i].className == 'item' && sidebarli[i].parentNode.className=='prod'){ 
sidebarli[i].onclick = function(){   
alert(this.id+': Is sending your request.');
}
if(sidebarli[i].className == 'item' && sidebarli[i].parentNode.className=='site'){ 
sidebarli[i].onclick = function(){   
alert(this.id+': Is Not sending your request.');
}
else{ //IE8 throws syntax error here can't get if else in for statement syntax?
sidebarli[i].onclick = function(){
alert(this.id);
}}

centerd effect Thanks for your patience. I was trying to avoid writting a seperate script for each ul in the #sidebar div. If if and else statements can’t do it then maybe a switch case could be used with sidebarli i. I know I’m asking a lot and really should concider the ability of javascripts to handle the situation after all server scripts will be in place to handle the elements in the HTML anyway. There is an iObj technique that I’ve used that I place in addEvent window onload but I’m not sure I could put multiple if and else statements in that model either.

I’m guessing the ones with classname “disabled” are supposed not to work? If so –


var sidebarli=document.getElementById('sidebar').getElementsByTagName('li');   
for (i = 0; i < sidebarli.length; i++) {   
  if (sidebarli[i].className.indexOf('disabled') != -1) {  // excludes items with class "disabled" 
    sidebarli[i].onclick = function() {   
      alert(this.id+': Is sending your request.');
      var p=this.id; 
      getproduct(p);
    }   
  }   
}

The trick is in sidebarli[i].className.indexOf('disabled') != -1. The .indexOf gives you the index of a string within another string. As a basic example, “ScallioXTX”.indexOf(“callio”) is 1 (indexes are 0-based).
If a string can’t be found at all, the function returns -1. So if sidebarli[i].className.indexOf(‘disabled’) != -1 is true, the class ‘disabled’ is not among the classes set on the node.
The nice thing about using indexOf is that it doesn’t matter where you put the ‘disabled’ class. ‘item disabled’ works, as does ‘disabled item’, as does ‘handbag lightbulb disabled charlie sheen’

Also, please add some checking to the PHP to check if the table name is amongst the ones that are allowed to be queried. The way you’ve set it up now makes my left eye twitch a little bit …


if (in_array($id, array('baskets', 'books', 'etc', 'etc')) {
 // yup, that's okay
} else {
 // request not allowed
}

:slight_smile:

PS.

Don’t bother, no-one else does :wink:

ScallioXTX Thanks for the input, the syntax is right, now I’m strugling to put two ifs and an else into, the ‘for in’ statement and I keep getting syntax error at the ‘else{’ part. The truth is that the first 3 uls are the only ones that might have a class of ‘item disabled’. They are a means of showing the client what we might have comming to the site. They also provide me with the ability not to run the httprequest. They might also have a class of ‘item visited’ which would allow the client to revisit and not block the httprequest which was not in the previous script.
IF, IF & ELSE


var sidebarli=document.getElementById('sidebar').getElementsByTagName('li');   
for(i = 0; i < sidebarli.length; i++) {  
if(sidebarli[i].className == 'item' && sidebarli[i].parentNode.className=='prod'){ 
sidebarli[i].onclick = function(){   
alert(this.id+': Is sending your request.');
}
if(sidebarli[i].className == 'item' && sidebarli[i].parentNode.className=='site'){ 
sidebarli[i].onclick = function(){   
alert(this.id+': Is Not sending your request.');
}
else{ //IE8 throws syntax error here can't get if else in for statement syntax?
sidebarli[i].onclick = function(){
alert(this.id);
}}

As you can see it is really a basic javascript syntax that I’m not getting right. After I do I can add the functions. I couldn’t find ‘IF ELSE IN FOR IN STATEMENT’ when I searched the web, even though I’ve seen it before, my brain is stuck in bobo.