How do you LOAD MySQL in HTML5 mode correctly?

karmacomposer · **Posted:** September 4th, 2015, 10:59 am

I changed the title and need - I have done everything I can do to LOAD a database record into a form with all the fields setup but nothing is coming in.

I believe it has to do with javascript or php and not Opus Pro.

Can anyone advise me on HOW to use the proper javascript, opusscript or php and WHERE it should go to load a database in and be able to navigate from record to record, back and forth, and be able to search via one field to display that particular record.

Thanks.

Mike

mackavi · **Posted:** September 7th, 2015, 5:42 pm

The postwebdata action is available in HTML5.

The listbox is not - but you can build one manually using the HTML5 object. I think the topic has already been discussed.

</mack>

karmacomposer · **Posted:** September 7th, 2015, 9:05 pm

I actually already successfully saved to a MySQL database using Post to Web in HTML5 mode, but am having a terrible time LOADING and DISPLAYING records from the same MySQL database into a form with all the fields and everything is correctly set up.

I would LOVE some help.

Mike

mackavi · **Posted:** September 7th, 2015, 9:28 pm

Is a record echoing from the PHP correctly?

</mack>

karmacomposer · **Posted:** September 7th, 2015, 9:44 pm

Yes, it is.

Here is the php select code:

Code:

<?php

$DBhost = "mfelkerco.com";
$DBuser = "m_____r";
$DBpass = "V___________d";
$DBName = "mfelker_TB3Memdb";

$connect = mysqli_connect($DBhost,$DBuser,$DBpass);
$db = mysqli_select_db($connect,$DBName);
$query = "SELECT * FROM members";
$result = mysqli_query($connect,$query);

$row = mysqli_fetch_array($result);
$data = "&memberid=".$row['memberid']."&membername=".$row['membername']."&membertitle=".$row['membertitle']."&imgpicpath=".$row['imgpicpath']."&membercategory=".$row['membercategory']."&businessname=".$row['businessname']."&officephone=".$row['officephone']."&mobilephone=".$row['mobilephone']."&faxphone=".$row['faxphone']."&emailaddr=".$row['emailaddr']."&businesswebsite=".$row['businesswebsite']."&businessdesc=".$row['businessdesc']."&facebooklink=".$row['facebooklink']."twitterlink=".$row['twitterlink']."&linkedinlink=".$row['linkedinlink']."&youtubelink=".$row['youtubelink']."&myspacelink=".$row['myspacelink']."&instagramlink=".$row['instagramlink']."&googlepluslink=".$row['googlepluslink']."&pinterestlink=".$row['pinterestlink']."&isplatinummember=".$row['isplatinummember']."&memberjoindate=".$row['memberjoindate']."&bcfrontpic=".$row['bcfrontpic']."&bcbackpic=".$row['bcbackpic']."&platinumbannerpic=".$row['platinumbannerpic'];
echo $data;

?>

Anything look wrong?

How do I select from the array in Opus Pro? This is where I need help. Showing individual records and being able to cycle through them as well as searching for a specific one using eithe the MEMBER NAME or MEMBER CATEGORY fields (that is why I need a drop down).

Mike

mackavi · **Posted:** September 7th, 2015, 9:58 pm

That code will only return the first row to Opus.

You have to decide whether you are going to return one row at a time using a unique key in your query to find the right row OR read through the whole array whilst it's in the PHP script to format into something that can be echoed back to Opus and then decoded in Opus.

I take it you have checked to see that the row echoed in your script is actually reaching the browser / publication?

</mack>

karmacomposer · **Posted:** September 7th, 2015, 10:08 pm

Yes. I have received the 1st record with Opus Pro version - with HTML 5 version is DOES NOT work. I cannot figure out why. Everything is the same.

I even started over with a brand new HTML 5 project and made sure everything is the same.

I want to create the entire structure in PHP and then let Opus Pro read from those arrays.

Problem is, I am not sure how to accomplish this.

I can attach the project file if you wish.

Here it is in action:

http://www.tampabaybusiness.org/memberdir/memberdir.htm

Not sure why it does not work and when it did (as a Opus Pro Plexus project), it took forever to read the record (several minutes - it should take seconds, not minutes).

Also, the GO buttons are supposed to allow you to navigate to that URL using the data from the database, but they never worked.

The stylized boxes to the left are for pictures - simply web addresses that should show up in the picture boxes based off of stored URL locations in the database - those do
not work either.

Mike

mackavi · **Posted:** September 7th, 2015, 10:21 pm

Silly question - but the .htm file and the .php are in the same folder on the server?

No, the imp won't help, but if you give me the url or the files above, that might.

</mack>

karmacomposer · **Posted:** September 7th, 2015, 11:29 pm

The php file is at: http://www.mfelkerco.com/select.php

The htm file is at: http://www.tampabaybusiness.org/memberdir/memberdir.htm

They are on my server - the same server.

Do you think moving the select.php to the root of the tampabaybusiness.org directory would help?

I'll try it now and see if it makes a difference.

Mike

karmacomposer · **Posted:** September 7th, 2015, 11:35 pm

That made a HUGE difference.

The select.php is now at http://www.tampabaybusiness.org/memberdir/select.php

Now how do I cache the entire dataset into an array that I can then utilize throughout the Opus program?

As said before, I mostly need to be able to go back and forth between records or search for a specific name (membername) or a category (membercategory).

Thank you again for your help.

Mike

mackavi · **Posted:** September 8th, 2015, 9:19 am

PHP that will loop through all rows of a dataset query and return them as a single JSON string that can then be converted back to an array in JavaScript.

For an introduction to PHP / SQL error handling and security - I recommend reading one of the Visual QuickPro Guides on the subject before deploying any code to a live server.

BOOK: http://www.amazon.co.uk/PHP-MySQL-Dynam ... b_title_bk
FIREBUG: https://addons.mozilla.org/en-US/firefo ... src=search

Code:

<?php
/*
(C) interaktiv.co.uk - 2015
This is a basic example.
It contains no security.
It contains limited error handling.

1. You need to configure the database connection.
2. You need to set up your SQL statement.
3. You need to specify which fields will be used in the returned row.

a. Firstly check that the php runs in the browser.
b. Secondly check that it returns correctly to browser when called from Opus POST using something like Firebug for Firefox - see NET panel.
c. Lastly use JSON.parse within Opus to convert JSON back to a JavaScript array.  
*/

//1. Connect to Database.
$con=mysqli_connect("localhost","user","password","database");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

//2. Prepare the SQL statement.

$sql= "SELECT fields FROM table";

//Execute the SQL query.
$result=mysqli_query($con,$sql);


//Initialise the array for holding all the returned rows.
$echodata = array();

//fetch the rows and loop through them.
while($row = mysqli_fetch_array($result)) {
    //3. build a new array of the data from each row and add it to our returning array;
    $echodata[] = array($row[1],$row[2]);
    }

//convert our returning array to a JSON script for echoing.
echo json_encode($echodata);

// Free result set.
mysqli_free_result($result);
//close the connection.
mysqli_close($con);


?> 

</mack>

karmacomposer · **Posted:** September 9th, 2015, 9:47 pm

OK, here is a weird situation:

Why does this work when you run it on its own (username and password blocked out):

Code:

<?php
/*
===========================================================================================
TB3 Member Directory Selection Php Script

This script loads the member directory database and puts it
into an array for the main HTML 5 site to load and utilize
===========================================================================================
*/

//Define database login variables

$DBhost = "mfelkerco.com";
$DBuser = "m________r";
$DBpass = "V____________d";
$DBName = "mfelker_TB3Memdb";

//Login to database
$con = mysqli_connect($DBhost,$DBuser,$DBpass);

//Check to make sure login worked
if (mysqli_connect_errno())
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

//Select the database
$db = mysqli_select_db($con,$DBName);

//Set up the SQL query
$query = "SELECT * FROM members";

//Execute the sql query
$result = mysqli_query($con,$query);

//Initialize and create the array to hold the data
//$echodata = array();
$return_arr = array();

//Fetch the rows and loop through them
while($row = mysqli_fetch_array($result)){
   $row_array['memberid'] = $row['memberid'];
    $row_array['membername'] = $row['membername'];
    $row_array['membertitle'] = $row['membertitle'];
   $row_array['imgpicpath'] = $row['imgpicpath'];
    $row_array['membercategory'] = $row['membercategory'];
    $row_array['businessname'] = $row['businessname'];
   $row_array['officephone'] = $row['officephone'];
    $row_array['mobilephone'] = $row['mobilephone'];
    $row_array['faxphone'] = $row['faxphone'];
   $row_array['emailaddr'] = $row['emailaddr'];
    $row_array['businesswebsite'] = $row['businesswebsite'];
    //$row_array['businessdesc'] = $row['businessdesc'];
   $row_array['facebooklink'] = $row['facebooklink'];
    $row_array['twitterlink'] = $row['twitterlink'];
    $row_array['linkedinlink'] = $row['linkedinlink'];
   $row_array['youtubelink'] = $row['youtubelink'];
    $row_array['myspacelink'] = $row['myspacelink'];
    $row_array['instagramlink'] = $row['instagramlink'];
   $row_array['googlepluslink'] = $row['googlepluslink'];
    $row_array['pinterestlink'] = $row['pinterestlink'];
    $row_array['isplatinummember'] = $row['isplatinummember'];
   $row_array['memberjoindate'] = $row['memberjoindate'];
    $row_array['bcfrontpic'] = $row['bcfrontpic'];
    $row_array['bcbackpic'] = $row['bcbackpic'];
   $row_array['platinumbannerpic'] = $row['platinumbannerpic'];
   //$row_array['businessdesc2'] = $row['businessdesc2'];
   //$row_array['businessdesc3'] = $row['businessdesc3'];
   //$row_array['businessdesc4'] = $row['businessdesc4'];
   
    array_push($return_arr,$row_array);
}

//Convert array to JSON script
echo json_encode($return_arr);

//Free the result set
mysqli_free_result($result);

//Close the database connection
mysqli_close($con);

?>

and this does not work when you run it on its own (again, username and password blocked out):

Code:

<?php
/*
===========================================================================================
TB3 Member Directory Selection Php Script

This script loads the member directory database and puts it
into an array for the main HTML 5 site to load and utilize
===========================================================================================
*/

//Define database login variables

$DBhost = "mfelkerco.com";
$DBuser = "m________r";
$DBpass = "V____________d";
$DBName = "mfelker_TB3Memdb";

//Login to database
$con = mysqli_connect($DBhost,$DBuser,$DBpass);

//Check to make sure login worked
if (mysqli_connect_errno())
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

//Select the database
$db = mysqli_select_db($con,$DBName);

//Set up the SQL query
$query = "SELECT * FROM members";

//Execute the sql query
$result = mysqli_query($con,$query);

//Initialize and create the array to hold the data
//$echodata = array();
$return_arr = array();

//Fetch the rows and loop through them
while($row = mysqli_fetch_array($result)){
   $row_array['memberid'] = $row['memberid'];
    $row_array['membername'] = $row['membername'];
    $row_array['membertitle'] = $row['membertitle'];
   $row_array['imgpicpath'] = $row['imgpicpath'];
    $row_array['membercategory'] = $row['membercategory'];
    $row_array['businessname'] = $row['businessname'];
   $row_array['officephone'] = $row['officephone'];
    $row_array['mobilephone'] = $row['mobilephone'];
    $row_array['faxphone'] = $row['faxphone'];
   $row_array['emailaddr'] = $row['emailaddr'];
    $row_array['businesswebsite'] = $row['businesswebsite'];
    $row_array['businessdesc'] = $row['businessdesc'];
   $row_array['facebooklink'] = $row['facebooklink'];
    $row_array['twitterlink'] = $row['twitterlink'];
    $row_array['linkedinlink'] = $row['linkedinlink'];
   $row_array['youtubelink'] = $row['youtubelink'];
    $row_array['myspacelink'] = $row['myspacelink'];
    $row_array['instagramlink'] = $row['instagramlink'];
   $row_array['googlepluslink'] = $row['googlepluslink'];
    $row_array['pinterestlink'] = $row['pinterestlink'];
    $row_array['isplatinummember'] = $row['isplatinummember'];
   $row_array['memberjoindate'] = $row['memberjoindate'];
    $row_array['bcfrontpic'] = $row['bcfrontpic'];
    $row_array['bcbackpic'] = $row['bcbackpic'];
   $row_array['platinumbannerpic'] = $row['platinumbannerpic'];
   $row_array['businessdesc2'] = $row['businessdesc2'];
   $row_array['businessdesc3'] = $row['businessdesc3'];
   $row_array['businessdesc4'] = $row['businessdesc4'];
   
    array_push($return_arr,$row_array);
}

//Convert array to JSON script
echo json_encode($return_arr);

//Free the result set
mysqli_free_result($result);

//Close the database connection
mysqli_close($con);

?>

All I did to make the php script to work is comment out the 4 variables ('businessdesc', 'businessdesc2', 'businessdesc3' and 'businessdesc4')

I originally only had 'businessdesc' as the field and it STILL did not work without commenting that field out.

Why are those causing no data to show? If commented out, then all other data shows without a problem.

Am I using a reserved keyword or something?

The businessdesc fields are mediumtext like most the other fields.

Now that I ALMOST have an array that holds all data, I need to figure out how to assign those array fields ($row[field]) to the individual text boxes.

Any ideas on why the data is not showing because of the businessdesc fields?

Thank you.

Mike

karmacomposer · **Posted:** September 10th, 2015, 4:25 am

The answer is NOTHING. Neither were wrong.

It was the data causing the problem. Too many sentences. I pruned the crap out of the offending fields and now it works 100%.

Now, how do I get it into my Opus Pro app?

I need to populate the fields with the data, being able to go back and forth between records.

The data is now truly in an array, so what do I do now?

Mike

mackavi · **Posted:** September 11th, 2015, 3:26 pm

Yes, data needs to be stored security and correctly or it will cause all manor of nasty problems.

As for the return, once you've run JSON.parse - you'll have an array - IE

Code:

myArray = window.JSON.parse(myReturnData)

You can then assign array elements to the variables in your form - IE

Code:

myName = myArray[0]

Note, however, that you may have created a multidimensional array so will have to specify the element of the array within in the array - IE

Code:

myName = myArray[0][0]

[EDIT] should read window not windows in code above.

</mack>

karmacomposer · **Posted:** September 11th, 2015, 7:39 pm

@mack:

Thank you so much for your help. I will take this info and try my best to get it to work.

Mike

Digital Workshop

How do you LOAD MySQL in HTML5 mode correctly?

Who is online