Tuto Database

#1 · December 3, 2019, 4:52 pm

Hello Luis,

Is it possible to have a "full" tuto of how to use à Mysql or Sqlite database ?

By "full", I mean :

ENTER data (from text input), DELETE data, SEARCH data, MAKE A LIST ( in combobox or listbox) of a peculiar field of the database, how to STORE PICTURES as data...

And an other question, do you plan to add video on your YOUTUBE channel ?

I do like tutoriels by video.
Presently, I follow videos courses about html5, CSS, PHP and Myphpadmin... very interesting !!

I would like to became an "expert" (lol) of VisualNeo Web, cause I do like the software !!!

Best regards,

#2 · December 3, 2019, 6:30 pm

Hi @cdy44-2,

Yes, I want to add those tutorials in video and/or text format.
Unfortunately currently I do not have time enough but I'm very aware about the importance of such information.
Meanwhile please take a look at the included samples and ask whatever you need here in the forums.
Thank you for your understanding.

Best regards.

#3 · December 5, 2019, 12:26 am

Hi Luis,

To INSERT data in a database, is the following correct, in config.php ?

$sqlAlias[1]="insertData";
$sqlQuerys[1]="INSERT INTO a_table (field1, field2, field3) VALUES ('test', 'N', NULL)";
$sqlMaxUserLevel[1]=-1;

Regards,

#4 · December 5, 2019, 10:06 am

I think so, but be sure to start each array index with [0] instead of [1].
If you have a previous SQL Query with [0] index then it's correct.
Try modifying the included sample apps to start learning.
Best regards.

#5 · December 5, 2019, 4:27 pm

2 questions...

--------------------------------------------------------------------------------------------------------------------------------------------

- How can I save the value ( [val1] ) inserted in a "TextInput", in my database.

I tried this :

$sqlAlias[0]="insertData";
$sqlQuerys[0]="INSERT INTO Paintings (painting, author) VALUES ([val1], [val2])";
$sqlMaxUserLevel[0]=-1;

...but it doesn't work.

This works :

$sqlAlias[0]="insertData";
$sqlQuerys[0]="INSERT INTO Paintings (painting, author) VALUES ('toto', 'tutu')";
$sqlMaxUserLevel[0]=-1;

...But I want insert data from variables ( variables associated with "TextInput" )

----------------------------------------------------------------------------------------------------------------------------------------------

- I use PhpLiteAdmin to create my database. When I export the database, the file extension is "sql". In your example, the extension of your database is "db", does it matter ?

Sorry for these questions, but I really start from scratch in my mind.....

Best regards,
Denis

#6 · December 6, 2019, 5:38 pm

Note that you can not use VisdualNEO Web variables directly in your .php script

So this is INCORRECT:

$sqlQuerys[0]="INSERT INTO Paintings (painting, author) VALUES ([val1], [val2])";

This is CORRECT:

$sqlQuerys[0]="INSERT INTO Paintings (painting, author) VALUES (?, ?)";

Just use a "?" in the place where you want to insert a VisualNEO Web variable value.

It's in your VisualNEO Web script calling the SQL Query where you have to add your variables as parameters sepatated by "::".
Something like this:

neoPhpExecSql "mydb" "insertData" "[val1]::[val2]" ""

I hope it helps.

Regars.

CDY@44 has reacted to this post.

#7 · December 6, 2019, 11:48 pm

Thank you Luis ! I will try this tomorrow.
Best regards,

#8 · December 7, 2019, 6:20 pm

Thank you, it works !!!

luishp has reacted to this post.

#9 · December 7, 2019, 9:17 pm

Great. Once you get how it works it's quite easy :)

#10 · December 7, 2019, 9:54 pm

Yes !!

A) Now I try to "DELETE a row... (DELETE FROM table_name WHERE id=?";

I don't know how to delete a single row ( displayed in a container, [id], [surname], [name])

B) I also like to learn how to make a SEARCH in database of a data in a [variable]

Regards,

#11 · December 14, 2019, 12:27 am

Hi Luis,

I tried this to DELETE a row in my database, but it failed...nothing changed...

neoPhpExecSql "db1" "deleteData" "[tableData([currentRecord])('id')]::[tableData([currentRecord])('nom')]::[tableData([currentRecord])('prenom')]" "ParseData"

and in "config.php"

$sqlAlias[2]="deleteData";
$sqlQuerys[1]="DELETE FROM users WHERE id=?";
$sqlMaxUserLevel[1]=-1;

I didn't try yet for SEARCH, but I think that I need help also....

Best regards,

#12 · December 14, 2019, 11:29 am

@cdy44-2 as you are using a single parameter in your SQL query (just one "?") you don't have to send more than one parameter from your App:

SetVar [idToDelete] [tableData([currentRecord])('id')]
neoPhpExecSql "db1" "deleteData" "[idToDelete]" "ParseData"

I hope it helps.
Regards.

#13 · December 14, 2019, 11:31 am

Also I have noticed you are not using the same array number here:

$sqlAlias[2]="deleteData";
$sqlQuerys[1]="DELETE FROM users WHERE id=?";
$sqlMaxUserLevel[1]=-1;

It should be:

$sqlAlias[2]="deleteData";
$sqlQuerys[2]="DELETE FROM users WHERE id=?";
$sqlMaxUserLevel[2]=-1;

Remember not to jump numbers (0,1,2,3...) never (0,2,3,5...)
Regards.

#14 · December 14, 2019, 12:41 pm

Thank you so much !!!
I am really silly !!
Thank you for you kindness !!
Best regards,

#15 · December 16, 2019, 12:42 am

Hi Luis,

Let's imagine I have a database with 3 columns, "id", "firstname", "name"
and I also have a COMBO BOX or DROPDOWN in my project.

How can I fill my combo box or my dropdown, with data which are in "name" column ?

Hope you have an idea.
Best regards,

#16 · December 16, 2019, 8:12 pm

How can I fill my combo box or my dropdown, with data which are in "name" column ?

You can use different approaches. The easiest is a simple SQL query to get only the desired data:

SELECT name FROM myTableName

I hope it helps.

#17 · December 16, 2019, 8:41 pm

Thank you.

But I must make a loop to get all the data 'name' each one after the other ?
If I have X rows in my database, how to get the X values in 'name' column ?

Best regards,

#18 · December 17, 2019, 1:56 pm

Please @cdy44-2 take a look at the included samples.
With "SELECT name FROM myTableName" you wil get ALL the records in a JSON object that you can then loop.
There are many possibilities to get only a subset of all the records. That's the reason the whole SQL language exists.
Here is a good tutorial to get started:
https://www.w3schools.com/sql/

Best regards.

CDY@44 has reacted to this post.

#19 · December 17, 2019, 5:53 pm

Thank you Luis,
I try different things with SQL, and for a newbie like me, it is very interesting to discover step after step.
I am sorry to annoy community with my questions. I search on the web for tutos, but sometime I feel stuck.
I will check the link !
Best regards,

#20 · December 21, 2019, 1:14 am

Hi Luis,

Sorry again...
I twisted my mind to find an easy solution, but I am stuck...

You said "...ALL the records in a JSON object that you can then loop." How can I do that ?

If you don't have time to answer me know, don't worry...
Best regards

Tuto Database - Forum

Tuto Database