I have some experience of PHP but limited knowledge of handling arrays.
My db table has 5000+ rows and 13000 populated fields. I want to create an alphabtical, visible index of the contents of each field, possibly for inclusion in an html page. If this possible, can it be initiated through an array. I can SELECT an array of all rows or by alphabet letter.
If not, is javascript a better option.
Can someone kindly point me in right direction and start me off ?
furlanut 0 Newbie Poster
imBaCodes 2 Junior Poster
Javascript would be nice for that. Try searching for JSON tuitorials its popular nowadays and can help you on problems like that.
pzuurveen 90 Posting Whiz in Training
5000+ rows and 13000 populated fields
sound like a job for the database itself. It's designed to handle large amount of data.
In php you can run into memory problems and/or long prosessing times.
and sending it all over the net so that javascript can handlel it, will increase load times
pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster
How about a tool like datatables.net?
annaharris 0 Junior Poster in Training
According to me, Javascript would be a better option.
paulkd 59 Newbie Poster
Are you saying you have a database table with 2, possibly 3 columns?
diafol
I'm assuming that you wouldn't be showing 5000+ records at any one time. Your page would be ridiculously long. Have you thought about pagination? That is just showing the first 50 records with pagelinks for the next 50 etc?
You have a few ways to do this. If you really need to dump 5000+ records into the page, then I really don't see any advantage to using js as php has to process it first anyway before formatting the data for js to stick its oar in.
If however, you're going to chunk up the data into pages, then you may wish to use javascript / ajax.
Depending on the volatility of your data, you could dump it all into a json on page load and then just display the first 50. Then on page click, you just get the data from the json to overwrite your html table. This is just a simple js solution, not requiring ajax nor page refresh.
Alternatively, you can use ajax to retrieve specific data from the DB. This would be handled via the LIMIT clause, so for example:
... LIMIT(0,50) //first page of 50 records
... LIMIT(50,50) //second page of 50 records
The last method would be to use vanilla php with non-js-hijacked links like:
<a href="?page=3">3</a>
That would reload the page and retrieve the data as in the previous method.
I won't go into more detail as you need to tell us how you think you need to proceed and have a go at producing your own code.
furlanut 0 Newbie Poster
Thank you all so far for your comments. I was thinking maybe of showing records by alpha letter, so maybe a few 100 at a time. Anyway I will mull things over.
furlanut 0 Newbie Poster
I have persevered with AJAX/Javascript (as a way of learning) and ended up being able to display all 13000 entries ! This is because I do not know how to change variable $name for each alpha letter.
What I would like to do is click on letters A to Z and have the query SELECT only those names in the range A,B,C etc.
Can someone help me with this,please ?
My Javascript, HTML and PHP are:
<html>
<body>
<script language="javascript" type="text/javascript">
<!--
//Browser Support Code
function ajaxFunction(){
var ajaxRequest; // The variable that makes Ajax possible!
try{
// Opera 8.0+, Firefox, Safari
ajaxRequest = new XMLHttpRequest();
} catch (e){
// Internet Explorer Browsers
try{
ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try{
ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
} catch (e){
// Something went wrong
alert("Your browser broke!");
return false;
}
}
}
// Create a function that will receive data sent from the server
ajaxRequest.onreadystatechange = function(){
if(ajaxRequest.readyState == 4){
document.getElementById('directory').innerHTML= ajaxRequest.responseText;
}
}
ajaxRequest.open("GET", "select_Name.php", true);
ajaxRequest.send(null);
}
//-->
</script>
<form name='myForm'>
<button type='button' onclick='ajaxFunction();' />A</button><button type ='button' onclick ='ajaxFunction();'/>B</button>
</form>
<div id="directory" style="background-color:yellow;height:auto;width:25%;">display output here</div>
</body>
</html>
<?php // select $name
require_once "..php/dbconnect.php";
$db_server = mysql_connect('localhost','root','');
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db('shipdb')
or die("Unable to select database: " . mysql_error());
$name='';
$name=strtoupper($name);
$name=strip_tags($name);
$name=trim($name);
$query="(SELECT name1 FROM allnames WHERE name1 LIKE '$name%')UNION(SELECT name2 FROM allnames WHERE name2 LIKE '$name%')UNION(SELECT name3 FROM allnames WHERE name3 LIKE '$name%')UNION(SELECT name4 FROM allnames WHERE name4 LIKE '$name%')UNION(SELECT name5 FROM allnames WHERE name5 LIKE '$name%')ORDER BY 1";
//$query="SELECT name1 FROM allnames WHERE name1 LIKE 'costa%'";
$result=mysql_query($query);
if(!$result) die ("Database access failed:" .mysql_error());
$num_rows=mysql_num_rows($result);
if(mysql_num_rows($result)==0)
{
echo"<p><font color='red' size='4'>No data found for your request. Try another query</font>";
}
while($row=mysql_fetch_assoc($result))
{
$array=$row;
foreach ($array as $var)
{
if(!empty($var))
{
$str=($var);
$str=strtoupper($str);
echo"$str<br/>";
}
}
}
?>
diafol
<?php
$filters = '';
$defaultChar = 'A';
$labelSearch = range('A', 'Z');
foreach($labelSearch as $char)
{
$check = ($defaultChar == $char) ? ' checked="checked"' : '';
$filters .= "<label class=\"toggle-btn\"><input type=\"radio\" name=\"filter\" value=\"$char\"$check />$char</label>\n";
}
?>
<!DOCTYPE HTML>
<html>
<head></head>
<body>
<?php
echo $filters;
?>
<div id="mydata">
<!-- this is where you put your data -->
</div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.2/jquery.min.js"></script>
<script>
getData('A'); //this is the function call to populate the mydata with default data on page load
$(".toggle-btn:not('.noscript') input[type=radio]").change(function() {
chr = $(this).val();
alert(chr); //testing
getData(chr);// function call on change
});
function getData(chr)
{
//this is your ajax call to a php script to get data and to display it in 'mydata' div
}
</script>
</body>
</html>
That's a quick draft using jQuery and a bit of php. In fact you can do without the php if you want to hard code the html for the load page. It just saves about 20 lines of markup.
The getData function simply sends the character to your php script. You do the mysql stuff and return any data. Pick it up in js and write to 'mydata'. Pretty standard stuff. I sujjest using json format for the data. You'll need to:
echo json_encode(...a php array of the data...);
in your php file in order to pass structured data that js can understand. Well, you don't have to, but it's very straightforward compared to other methods.
There are other ways of doing this, but I think this is the easiest.
furlanut 0 Newbie Poster
Thanks diafol for a very concise and full reply.
However, I am having difficulty understanding the first 8 lines.
In particular the item $filters.
I enter the code
$filters="<label...
etc
(is that a full stop after $filters ?) and an input box appears containing
\"$char\"$check/
and no radio button.
If I remove the escape slashes I get a radio button and no input box.
Is there some code missing ? or am I missing the obvious ?
Grateful if you can throw further light on this which goes a little further than my experience to date.
diafol
Yes you must have the period. THis appends new label/inputs to the existing string - so the string builds up to contain the whole A-Z. Did you try the full code? Didn't you see something like this?
furlanut 0 Newbie Poster
Thanks, with the period and some accurate typing I have the row of radio buttons A-Z but no alert box.
As I load the page the FULL list of records is displayed in the div, BEFORE I can select any button. When I do select a button no change happens.
I suspect there is some disconnect between getData('A') and my php/MySQL page ?
The ajax call goes to the php described above (2 weeks ago).
furlanut 0 Newbie Poster
What I should have said was:
I now have the alert, but when I OK the letter presented no change happens (i.e. the full list has already been returned to the div)
diafol
Yes, well you need to default the 'A' maybe on load. The fact that it's not refreshing the div is down to your ajax code, not my js :)
This'll get you on the right trach (I hope):
<?php
$filters = '';
$defaultChar = 'A';
$labelSearch = range('A', 'Z');
foreach($labelSearch as $char)
{
$check = ($defaultChar == $char) ? ' checked="checked"' : '';
$filters .= "<label class=\"toggle-btn\"><input type=\"radio\" name=\"filter\" value=\"$char\"$check />$char</label>\n";
}
?>
<!DOCTYPE HTML>
<html>
<head></head>
<body>
<?php
echo $filters;
?>
<div id="mydata">
<!-- this is where you put your data -->
</div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.2/jquery.min.js"></script>
<script>
$(".toggle-btn:not('.noscript') input[type=radio]").change(function() {
chr = $(this).val();
getData(chr);// function call on change
});
function getData(chr)
{
var c = chr;
$.post("ajax.php", { "chr": c },
function(data){
alert(data.first);
}, "json");
};
getData('A');
</script>
</body>
</html>
ajax.php file:
if(isset($_POST['chr']) && in_array($_POST['chr'], range('A', 'Z'))){
$r = array("first"=>"output1","second"=>"output2");
echo json_encode($r);
}
furlanut 0 Newbie Poster
Thank you, but now you have eliminated the ajaxRequest code I cannot grasp how your code is linking the input page to the MySQL query or variable. Also have no clue where the 'if(isset..) goes in the MySQL page. Have tried various positions but can get no returns to the input page.
Rather than take up more of your time I will stick to what I know and maybe return to it another time.
diafol
Time, sweet time...
The ajax.php file runs the mysql...
if(isset($_POST['chr']) && in_array($_POST['chr'], range('A', 'Z'))){
$chr = $_POST['chr'];
$start = 0; //ideally this would be passed by the same js as chr in order to apply pagination
//do connection details - I'll use mysql to keep it simple, but consider using mysqli or PDO
$q = mysql_query("... your SQL ...");
//check there are results before this...
$r = array();
while($d = mysql_fetch_assoc($q){
$r[] = $d['fieldname']; // whatever the fieldname you're using in UNION.
}
echo json_encode($r);
}
That should return data as json to the js script.
Then js deals with it in the way that you want
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.